时间戳

首页 > 解决方案 > 带有计数和日期的 SQL 联合

sql - 带有计数和日期的 SQL 联合

问题描述

再次询问，但希望更清楚。我有以下情况完美运行您可以查看Sql Unions 第 1 部分，了解已回答的原始问题！

DECLARE @imvw_woap_code_with_cust TABLE (
        pkey INT,
        Code_id VARCHAR(200),
        code_type_id VARCHAR(200),
        asset_standard_id VARCHAR(200),
        date_completed DATETIME
    )
INSERT INTO @imvw_woap_code_with_cust(pkey,Code_id,code_type_id,asset_standard_id,date_completed)
    SELECT 101480,'Alarmining','A-Problem','1 East','2017-08-10 02:00:30.013'
    UNION SELECT 103350,'Fluid/Gas Leak (Out)','A-Problem','2 west','2017-08-10 02:00:30.013'
    UNION SELECT 104033,'plug/Pwr Cord Damg','A-Problem','2 west','2017-08-10 02:00:30.013'
    UNION SELECT 107296,'Alarmining','A-Problem','2 west','2017-09-11 02:00:30.013'
    UNION SELECT 106404,'Incoming Inspect','A-Problem','2 west','2017-08-10 02:00:30.013'
    UNION SELECT 106407,'Incoming Inspect','A-Problem','2 west','2017-09-11 02:00:30.013'
    UNION SELECT 106408,'Incoming Inspect','A-Problem','2 west','2017-09-12 02:00:30.013'

DECLARE @IMTBL_CODE TABLE (
    pkey INT,
    Code_id VARCHAR(200),
    code_type_id VARCHAR(200)
)

INSERT INTO @IMTBL_CODE(pkey,Code_id,code_type_id)
    SELECT 100064,'1-NO PROBLEM CODE ENTERED','A-Problem'
    UNION SELECT 100068,'Alarmining','A-Problem'
    UNION SELECT 100069,'Battery Failure','A-Problem'
    UNION SELECT 100070,'Incoming Inspect','A-Problem'
    UNION SELECT 100071,'Fluid/Gas Leak (Out)','A-Problem'
    UNION SELECT 100072,'Case Damaged','A-Problem'
    UNION SELECT 100073,'plug/Pwr Cord Damg','A-Problem'


CREATE VIEW cause_codes as
SELECT
        possibilities.code_id AS code_id,
        possibilities.asset_standard_id,
        Count(U.pkey) AS [COUNT]
FROM (
    SELECT
        a.asset_standard_id,b.code_id
    FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
    CROSS JOIN (
        SELECT DISTINCT code_id FROM @imtbl_code
        UNION
        SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust 
    ) AS b
) AS possibilities
LEFT OUTER JOIN @imvw_woap_code_with_cust AS U ON U.code_id = possibilities.code_id
    AND possibilities.asset_standard_id=u.asset_standard_id
    and u.code_type_id='A-Problem'
GROUP BY possibilities.code_id, possibilities.asset_standard_id
order by [count]

一旦我将它创建到一个视图（cause_codes）中，它就会返回一个整洁的表，其中包含代码出现次数的计数（也包括 0 个计数）。

CODE ID                   asset_standard_id     count
1-NO PROBLEM CODE ENTERED   1 East                0
1-NO PROBLEM CODE ENTERED   2 west                0
Battery Failure             1 East                0
Battery Failure             2 west                0
Case Damaged                1 East                0
Case Damaged                2 west                0
Fluid/Gas Leak (Out)        1 East                0
Incoming Inspect            1 East                0
plug/Pwr Cord Damg          1 East                0
plug/Pwr Cord Damg          2 west                1
Fluid/Gas Leak (Out)        2 west                1
Alarmining                  1 East                1
Alarmining                  2 west                1
Incoming Inspect            2 west                3

然后客户回来并希望添加日期所以如果我在视图中添加日期

ALTER VIEW cause_codes as
    SELECT
            possibilities.code_id AS code_id,
            possibilities.asset_standard_id,
            Count(U.pkey) AS [COUNT],
            U.date_created
    FROM (
        SELECT
            a.asset_standard_id,b.code_id
        FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
        CROSS JOIN (
            SELECT DISTINCT code_id FROM @imtbl_code
            UNION
            SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust 
        ) AS b
    ) AS possibilities
    LEFT OUTER JOIN @imvw_woap_code_with_cust AS U ON U.code_id = possibilities.code_id
        AND possibilities.asset_standard_id=u.asset_standard_id
        and u.code_type_id='A-Problem'
    GROUP BY possibilities.code_id, possibilities.asset_standard_id,date_created
    order by [count]

然后进行搜索

 select * from cause_codes where asset_standard_id = '2 west' and date_completed > '2017-09-10 02:00:30.013' and date_completed < '2017-09-12 02:00:30.013'

该表像这样拆分行 ->

Incoming Inspect       2 west                1   2017-09-11 02:00:30.013' 
Incoming Inspect       2 west                1   2017-09-12 02:00:30.013'

有没有办法可以做到这一点（添加一个搜索日期），但仍然保持单行的计数正确？

我希望这很清楚......这是一个相当大的问题。感谢您花时间阅读我的书 :)

标签： sqlsql-server

解决方案

每当您按日期分组并且该日期有多个唯一值时，您将获得每个日期的多行，每个唯一日期的分组总和。请注意，您现在要求提供每个代码、资产 ID 和日期的汇总摘要数据。

您必须获取新日期值的最小值或最大值（不同的摘要），而不是按日期分组，或者如果您愿意并且确定不会获得大量日期，您可以列出如果您想这样做，它们会在一个逗号分隔的列表中列出可能的日期。这是一个使用 For Xml 技巧从其他表中区分日期列表的示例。网上还有其他例子。

select
    RepName, DateList
from
    (
        select
            FirstName + ' ' + LastName as RepName, PersonID
        from
            Person
        where
            PersonID in (23164,30637,29985,25643)
    ) as p
    cross apply (
        select stuff ( (
            select
                ', ' + convert(varchar(16), CallDate, 101)
            from    
            (
                select
                    distinct TheDate
                from
                    dbo.Info as c
                    inner join Person as p2 on p2.PersonID = c.PersonID
                where
                    p2.PersonID = p.PersonID
            ) as dat
            order by CallDate
            for xml path('')
        ), 1, 2, '') as DateList
    ) as dateList

推荐阅读