Sql

递归查询设备组

WITH ResourceHierarchy (resourcegroupid, resourcegroupname) AS (
    SELECT rg.resourcegroupid, rg.resourcegroupname
    FROM RESOURCEGROUP rg
    WHERE rg.resourcegroupname = '.Spec-GZ'

    UNION ALL

    SELECT rg.resourcegroupid, rg.resourcegroupname
    FROM RESOURCEGROUPGROUPS rgg
    JOIN ResourceHierarchy rh ON rgg.resourcegroupid = rh.resourcegroupid
    JOIN RESOURCEGROUP rg ON rg.resourcegroupid = rgg.groupsid
)
SELECT DISTINCT r.resourcename AS "Value", r.resourcename AS "DisplayName"
FROM ResourceHierarchy rh
JOIN RESOURCEGROUPENTRIES rge ON rh.resourcegroupid = rge.resourcegroupid
JOIN RESOURCEDEF r ON r.resourceid = rge.entriesid;

行列转换

SELECT
    COALESCE(Column1, '物料名称') AS 物料名称,
    COALESCE(Column2, '规格') AS 规格,
    COALESCE(Column3, '供应商') AS 供应商
FROM
(
    SELECT 
        dcdb.datacollectiondefname,
        dcd.inspecttype AS type,
        dcd.datacollectiondefrevision,
        dphd.dataname,
        dphd.datavalue,
        hm.txndate,
        hm.txnid,
        hm.comments
    FROM resourcedef rd
    LEFT JOIN datapointhistory dph ON dph.historyid = rd.resourceid
    LEFT JOIN datapointhistorydetail dphd ON dphd.datapointhistoryid = dph.datapointhistoryid
    LEFT JOIN datacollectiondef dcd ON dcd.datacollectiondefid = dph.datacollectiondefid
    LEFT JOIN datacollectiondefbase dcdb ON dcdb.datacollectiondefbaseid = dcd.datacollectiondefbaseid
    LEFT JOIN historymainline hm ON hm.historymainlineid = dph.historymainlineid
    WHERE dcd.inspecttype = N'铜箔'
)
PIVOT
(
    MAX(datavalue)
    FOR dataname IN ('物料名称' AS Column1, '规格' AS Column2, '供应商' AS Column3)
)
ORDER BY datacollectiondefname, txndate;

查询VP菜单

Select
  pp.portalmenudefinitionname
From portalmenuitem pm
Inner Join portalmenudefinition pp On pm.parentid = pp.portalmenudefinitionid
Inner Join uivirtualpage vp On vp.uivirtualpageid = pm.virtualpageid
Where vp.uivirtualpagename = 'SS_ReworkVP';

查询当前登录用户信息

1	SELECT * FROM activeusersession;

Query

矩阵查询语句

SELECT
  Spec AS Spec
  , Product AS Product
  , Factory AS Factory
  , ProductTypeName AS ProductTypeName
  , ProductFamilyName AS ProductFamilyName
  , MaterialGroup AS MaterialGroup
  , PNUOM AS PNUOM
  , ProductionCheckType AS ProductionCheckType
  , Description AS Description
  , Name AS Name
  , RN AS RN
FROM
  (
    SELECT
      CASE WHEN IR.Wzmspecid IS NULL THEN NULL ELSE SB.SpecName || ':' || S.SpecRevision END AS Spec
      , CASE WHEN IR.Wzmproductid IS NULL THEN NULL ELSE PB.ProductName || ':' || P.ProductRevision END AS Product
      , CASE WHEN IR.WzmFactoryId IS NULL THEN NULL ELSE F.Factoryname END AS Factory
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN NULL ELSE PF.Productfamilyname END AS ProductFamilyName
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN NULL ELSE PT.Producttypename END AS ProductTypeName
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN NULL ELSE U.Uomname END AS PNUOM
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN NULL ELSE MG.Wzmmaterialgroupname END AS MaterialGroup
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN NULL ELSE CT.Wzmproductionchecktypename END AS ProductionCheckType
      , IR.Description AS Description
      , IR.Wzminspectrulematrixname AS Name
      , CAST(ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN IR.Wzmspecid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.WzmFactoryId IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN 0 ELSE 1 END DESC
      ) AS INT) RN
    FROM
      wzminspectrulematrix IR
      LEFT OUTER JOIN Spec S ON IR.Wzmspecid = S.Specid
      LEFT OUTER JOIN Specbase SB ON S.Specbaseid = SB.Specbaseid
      LEFT OUTER JOIN Product P ON IR.Wzmproductid = P.ProductId
      LEFT OUTER JOIN ProductBase PB ON P.ProductBaseId = PB.ProductBaseId
      LEFT OUTER JOIN Productfamily PF ON IR.WzmProductfamilyid = PF.Productfamilyid
      LEFT OUTER JOIN Producttype PT ON IR.Wzmproducttypeid = PT.Producttypeid
      LEFT OUTER JOIN Factory F ON IR.wzmFactoryId = F.FactoryId
      LEFT OUTER JOIN Uom U ON IR.Wzmpnuomid = U.Uomid
      LEFT OUTER JOIN Wzmmaterialgroup MG ON IR.Wzmmaterialgroupid = MG.Wzmmaterialgroupid
      LEFT OUTER JOIN Wzmproductionchecktype CT ON IR.Wzmproductionchecktypeid = CT.Wzmproductionchecktypeid

    WHERE
      (IR.Wzmspecid LIKE ?SpecId OR IR.Wzmspecid IS NULL)
      AND (IR.Wzmproductid LIKE ?ProductId OR IR.Wzmproductid IS NULL)
      AND (IR.Wzmfactoryid LIKE ?FactoryId OR IR.Wzmfactoryid IS NULL)
      AND (IR.Wzmproductfamilyid LIKE ?ProductFamilyId OR IR.Wzmproductfamilyid IS NULL)
      AND (IR.Wzmproducttypeid LIKE ?ProductTypeId OR IR.Wzmproducttypeid IS NULL)
      AND (IR.Wzmpnuomid LIKE ?PNUOMId OR IR.Wzmpnuomid IS NULL)
      AND (IR.Wzmmaterialgroupid LIKE ?MaterialGroupId OR IR.Wzmmaterialgroupid IS NULL)
      AND (IR.Wzmproductionchecktypeid LIKE ?CheckTypeId OR IR.Wzmproductionchecktypeid IS NULL)
  ) DesignerAdvancedQuery
  WHERE
  RN BETWEEN ?STARTROWNUM AND ?STOPROWNUM

矩阵精准查询语句

SELECT
  Spec AS Spec
  , Product AS Product
  , Factory AS Factory
  , ProductTypeName AS ProductTypeName
  , ProductFamilyName AS ProductFamilyName
  , MaterialGroup AS MaterialGroup
  , PNUOM AS PNUOM
  , ProductionCheckType AS ProductionCheckType
  , Description AS Description
  , Name AS Name
  , RN AS RN
FROM
  (
    SELECT
      CASE WHEN IR.Wzmspecid IS NULL THEN NULL ELSE SB.SpecName || ':' || S.SpecRevision END AS Spec
      , CASE WHEN IR.Wzmproductid IS NULL THEN NULL ELSE PB.ProductName || ':' || P.ProductRevision END AS Product
      , CASE WHEN IR.WzmFactoryId IS NULL THEN NULL ELSE F.Factoryname END AS Factory
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN NULL ELSE PF.Productfamilyname END AS ProductFamilyName
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN NULL ELSE PT.Producttypename END AS ProductTypeName
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN NULL ELSE U.Uomname END AS PNUOM
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN NULL ELSE MG.Wzmmaterialgroupname END AS MaterialGroup
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN NULL ELSE CT.Wzmproductionchecktypename END AS ProductionCheckType
      , IR.Description AS Description
      , IR.Wzminspectrulematrixname AS Name
      , CAST(ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN IR.Wzmspecid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.WzmFactoryId IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductfamilyid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproducttypeid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmpnuomid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmmaterialgroupid IS NULL THEN 0 ELSE 1 END DESC
      , CASE WHEN IR.Wzmproductionchecktypeid IS NULL THEN 0 ELSE 1 END DESC
      ) AS INT) RN
    FROM
      wzminspectrulematrix IR
      LEFT OUTER JOIN Spec S ON IR.Wzmspecid = S.Specid
      LEFT OUTER JOIN Specbase SB ON S.Specbaseid = SB.Specbaseid
      LEFT OUTER JOIN Product P ON IR.Wzmproductid = P.ProductId
      LEFT OUTER JOIN ProductBase PB ON P.ProductBaseId = PB.ProductBaseId
      LEFT OUTER JOIN Productfamily PF ON IR.WzmProductfamilyid = PF.Productfamilyid
      LEFT OUTER JOIN Producttype PT ON IR.Wzmproducttypeid = PT.Producttypeid
      LEFT OUTER JOIN Factory F ON IR.wzmFactoryId = F.FactoryId
      LEFT OUTER JOIN Uom U ON IR.Wzmpnuomid = U.Uomid
      LEFT OUTER JOIN Wzmmaterialgroup MG ON IR.Wzmmaterialgroupid = MG.Wzmmaterialgroupid
      LEFT OUTER JOIN Wzmproductionchecktype CT ON IR.Wzmproductionchecktypeid = CT.Wzmproductionchecktypeid
      
    WHERE
        ((?SpecId<>'%' and IR.Wzmspecid = ?SpecId) or (?SpecId='%' and IR.Wzmspecid is null))
        AND ((?ProductId<>'%' and IR.Wzmproductid = ?ProductId) or (?ProductId='%' and IR.Wzmproductid is null))
        AND ((?ProductFamilyId<>'%' and IR.Wzmproductfamilyid = ?ProductFamilyId) or (?ProductFamilyId='%' and IR.Wzmproductfamilyid is null))
        AND ((?ProductTypeId<>'%' and IR.Wzmproducttypeid = ?ProductTypeId) or (?ProductTypeId='%' and IR.Wzmproducttypeid is null))
        AND ((?FactoryId<>'%' and IR.Wzmfactoryid = ?FactoryId) or (?FactoryId='%' and IR.Wzmfactoryid is null))
        AND ((?PNUOMId<>'%' and IR.Wzmpnuomid = ?PNUOMId) or (?PNUOMId='%' and IR.Wzmpnuomid is null))
        AND ((?MaterialGroupId<>'%' and IR.Wzmmaterialgroupid = ?MaterialGroupId) or (?MaterialGroupId='%' and IR.Wzmmaterialgroupid is null))
        AND ((?CheckTypeId<>'%' and IR.Wzmproductionchecktypeid = ?CheckTypeId) or (?CheckTypeId='%' and IR.Wzmproductionchecktypeid is null))
  ) DesignerAdvancedQuery
  WHERE
  RN BETWEEN ?STARTROWNUM AND ?STOPROWNUM