SQL Union 结果分组按 smiliar 到 "LIKE"

使用Access 2007，我试图运行一个查询，输出并组织一长串联合查询到报告的数据列表中。报告是我的主管传递给我的东西，我希望SQL只是做腿部工作，这样我就可以导出到Excel文件，然后复制粘贴。问题:

我使用的数据是从另一个部门的单独的microsoft Excel中导入的，并且该职员不遵守基本的数据库规则，没有希望更改系统。"LOSING_UNIT"列有时包含多个示例(例如，一些LOSING_UNITS是"A Trp"，另一些是"A Trp B Trp C Trp"，除了空格外，没有逗号，分号或任何其他形式来确定下一个单元)

如果可能的话，我需要查询执行类似于groupby的Like关系，以便任何包含"a Trp"的记录成为已完成的总数，剩余的总数等(目前我的输出为

)。

Title                       LOSING_UNIT                                  Total
Completed Directives        A TRP 3-71 CAV                               12
Delinquent Directives       A TRP 3-71 CAV                                2
Missing GUIC                A TRP 3-71 CAV                                2
Remaining Directives        A TRP 3-71 CAV                                8
Missing GUIC                A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV   1
Remaining Directives        A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV   1

如果这是不可能的选项2:能够排序字符串的长度，以便所有单独的LOSING_UNITS出现在顶部，其次是字母顺序。我没能找到一种方法来做到这一点，而不会产生"聚合函数"错误。

Select 'Completed Directives' AS Title, LOSING_UNIT, Count(PSD_ID) AS Total
FROM [Lateral_Transfers_Closed_Canceled_LINKED]
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT
UNION
SELECT 'Remaining Directives', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT
UNION
SELECT 'Delinquent Directives' AS Title,LOSING_UNIT, Count(PSD_ID) AS TOTAL
FROM [Lateral_Transfers_LINKED] AS LT
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT
UNION
SELECT 'Missing GUIC', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT
UNION
SELECT 'Missing LUIC', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND LUIC Is Null
AND GUIC Is NOT Null
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT
UNION
SELECT 'On Hold', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT;

编辑:

为了更清楚一点，下面是我如何尝试根据其他文章调整SQLSQL查询-在UNION

中使用Order By

Select '' AS Title, LOSING_UNIT, '' AS Total
FROM(
Select 'Completed Directives', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_Closed_Canceled_LINKED]
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")
UNION
SELECT 'Remaining Directives', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
UNION
SELECT 'Delinquent Directives' AS Title,LOSING_UNIT, Count(PSD_ID) AS TOTAL
FROM [Lateral_Transfers_LINKED] AS LT
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")
UNION
SELECT 'Missing GUIC', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null
UNION
SELECT 'Missing LUIC', LOSING_UNIT, COUNT(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND LUIC Is Null
AND GUIC Is NOT Null
UNION 
SELECT 'On Hold', LOSING_UNIT, Count(PSD_ID)
FROM [Lateral_Transfers_LINKED] AS LT
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY LOSING_UNIT
ORDER BY LOSING_UNIT)
GROUP BY LOSING_UNIT
ORDER BY MID(LOSING_UNIT, 1, 5), LOSING_UNIT;

我还尝试将聚合放到第一个SELECT中，结果没有变化。

编辑:

a TRP 3-71 cav
A TRP 3-71 CAV B TRP 3-71 CAV C Co 3-71 CAV
a TRP 3-71 cav c TRP 3-71 cav HHT 3-71 cav
a TRP 3-71 cav b TRP 3-71 cav
a TRP 3-71 cav c TRP 3-71 cav
b TRP 3-71 cav
b TRP 3-71 cav a TRP 3-71 cav
b TRP 3-71 cav c TRP 3-71 cav
c TRP 3-71 cav b TRP 3-71 cav HHT 3-71 cav
c TRP 3-71 cav * hht 3-71 cav
ht 3-71 cav a TRP 3-71 cav c TRP 3-71 cav
ht 3-71 cav a TRP 3-71 cav
ht 3-71 cav a TRP 3-71 cav b TRP 3-71 cav c TRP 3-71 cav
ht3 -71 cav c TRP 3-71 cav

最终解决方案

Select 'Completed Directives' AS Title, ValuesToFind.LOSING_UNIT, Count(PSD_ID) AS Total
FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (STATUS = "Complete")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT
UNION
SELECT 'Remaining Directives', ValuesToFind.LOSING_UNIT, COUNT(PSD_ID)
FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT
UNION
SELECT 'Delinquent Directives' AS Title, ValuesToFind.LOSING_UNIT, Count(PSD_ID) AS TOTAL
FROM 
    [Lateral_Transfers_LINKED] AS LT
    left join [ValuesToFind] 
       on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE()
AND (LOSING_PARENT_UIC = "WJJ4AA")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT
UNION
SELECT 'Missing GUIC', ValuesToFind.LOSING_UNIT, Count(PSD_ID)
FROM 
   [Lateral_Transfers_LINKED] AS LT
   left join [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND ( GUIC Is Null OR GUIC = "Not Activated" )
AND LUIC Is NOT Null
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

UNION
SELECT 'On Hold', ValuesToFind.LOSING_UNIT, Count(PSD_ID)
FROM 
    [Lateral_Transfers_LINKED] AS LT
    LEFT JOIN [ValuesToFind] 
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.LOSING_UNIT + '*'
WHERE (LOSING_PARENT_UIC = "WJJ4AA")
AND (LT.SERIAL_NUMBER  LIKE "Temp. Hold")
GROUP BY ValuesToFind.LOSING_UNIT
ORDER BY ValuesToFind.LOSING_UNIT

非常感谢Kyle

创建一个表，其中一个字段包含您希望从LOSING_UNIT列中"分离"出来的所有值。我们把它命名为ValuesToFind。

那么你的查询看起来像这样:

select Count(PSD_ID), ValuesToFind.Value from 
[Lateral_Transfers_LINKED] LT
left join ValuesToFind
on LT.LOSING_UNIT LIKE '*' + ValuesToFind.Value + '*'
GROUP BY 
ValuesToFind.Value

显然，添加任何您想要限制数据的过滤器。

此外，我建议使用Switch函数将各种字段解码到Title列中，这样您就可以在一个地方控制映射逻辑。比如

  select 
    ValuesToFind.Value,
    Count(PSD_ID), 
    Switch(STATUS = "Complete", 'Completed Directives',
LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE(), 'Delinquent Directives',
GUIC Is Null OR GUIC = "Not Activated", 'Missing GUIC',
LUIC Is Null AND GUIC Is NOT Null, 'Missing LUIC',
LT.SERIAL_NUMBER  LIKE "Temp. Hold", 'On Hold',
1 =1, 'Remaining Directives') as Title
    from 
    [Lateral_Transfers_LINKED] LT
    left join ValuesToFind
        on LT.LOSING_UNIT LIKE '*' + ValuesToFind.Value + '*'
    GROUP BY 
        ValuesToFind.Value,
Switch(STATUS = "Complete", 'Completed Directives',
LT.SUSPENSE_DATE BETWEEN #1/01/2014# AND DATE(), 'Delinquent Directives',
GUIC Is Null OR GUIC = "Not Activated", 'Missing GUIC',
LUIC Is Null AND GUIC Is NOT Null, 'Missing LUIC',
LT.SERIAL_NUMBER  LIKE "Temp. Hold", 'On Hold',
1 =1, 'Remaining Directives')

更新:我看到你完成的指令来自另一个表，这取决于你是否将你的两个表联合在一起，然后执行此聚合或单独执行每个表上的每个聚合，然后UNION只是结果。

相关内容

最新更新

热门标签：