sql-此查询需要帮助-表a和表b连接两个表,并且不丢失数据计数

xxhby3vn  于 2021-07-26  发布在  Java
关注(0)|答案(0)|浏览(205)
---Table A:    
 CREATE TABLE TABLE_A (
           BadgeNum varchar(10),     --This is a persons unique number.
        Gender varchar(2),            ---Gender 'F' or 'M'
        Date_Sent date,              --- Date of Questionnaire
        Type_Status varchar(3),       --- A person can be Single or Married 
        Living_State  varchar(2),     --Person's living state
        S_Type_Type int ,   -- Value can be 1 or 0 
        Recipient_num int,             --Key to create grouping to put each person in category. Using Case Statement for this 'Billing_Transaction' or 'Online_Transaction' 
        MONTH,
        YEAR
        );

-- Table B:
    CREATE TABLE TABLE_B
(

    BadgeNumber varchar,       --- This is a persons unique number
    RespondedYear smalldate,    --- Year a person responded
    RespondedMonth smalldate,   --- Month a person responded
    Date_Process                -- This is in YYMMWW (Year,Month,Week)
    Value money,     --- Cost of the purchase
    State           --- Person's resides

    )

----Sample Data for TABLE_A---  
INSERT INTO TABLE_A
VALUES  ('11E2', 'F',  '07/20/2020',  'Single', 'NV' , '1' , '00001', '07', '2020');
VALUES  ('11E3', 'M',  '06/30/2020', 'Married', 'AZ' , '1' , '00001', '06', '2020');
VALUES  ('11E4', 'F',  '05/22/2019',  'Single', 'TN',  '1' , '00001', '05', '2019'); 
VALUES  ('11E5', 'M',  '05/30/2018', 'Married', 'NY' , '1' , '00001', '05', '2018' ); 
VALUES  ('11E6', 'F',  '03/25/2017',  'Single', 'CA' , '1' , '00001', '03', '2017');
VALUES  ('11E7', 'M',  '02/27/2017', 'Married', 'VT' , '1' , '00002', '02', '2017');
VALUES  ('11E8', 'F',  '03/01/2018',  'Single', 'AL',  '1' , '00002', '03', '2018');
VALUES  ('11E2', 'F',  '07/20/2020',  'Single', 'NV' , '1' , '00001', '07', '2020');
VALUES  ('11E3', 'M',  '06/30/2020', 'Married', 'AZ' , '1' , '00001', '06', '2020');
VALUES  ('11E4', 'F',  '05/22/2019',  'Single', 'TN',  '1' , '00001', '05', '2019'); 
VALUES  ('11E5', 'M',  '05/30/2018', 'Married', 'NY' , '1' , '00001', '05', '2018' ); 
VALUES  ('11E6', 'F',  '03/25/2017',  'Single', 'CA' , '1' , '00001', '03', '2017');
VALUES  ('11E7', 'M',  '02/27/2017', 'Married', 'VT' , '1' , '00002', '02', '2017');
VALUES  ('11E8', 'F',  '03/01/2018',  'Single', 'AL',  '1' , '00002', '03', '2018');

---SampleDate for TABLE_B       

INSERT INTO TABLE_B
VALUES  ('11E3', '2020',  '7',  '200208', '200', 'AZ' );
VALUES  ('11E2', '2018',  '5',  '180520', '300', 'NV' );
VALUES  ('11E4', '2018',  '3',  '180311', '200', 'TN' ) ;
VALUES  ('11E5', '2020',  '6',  '200416', '800', 'NY' );
VALUES  ('11E6', '2019', ' 5',  '191250', '500', 'CA' );
VALUES  ('11E7', '2018',  '3',  '180313', '100', 'VT' );
VALUES  ('11E8', '2019', ' 5',  '191251', '1000', 'AL' );

----Queries for Table A---
SELECT 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
,CASE WHEN Recipient_num IN ('00001') THEN 'Billing_Transaction'
WHEN Recipient_num IN ('00002') THEN 'Online_Transaction'
END as [Category]
,COUNT(DISTINCT case when [S_Type_Type] = '1' THEN 'BadgeNum' ELSE NULL END) as [Sent_Questions]
,COUNT([BadgeNum]) as [NumberOfBadges]

FROM [TABLE_A]

GROUP bY 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,

--- Queries for Table B----
Table B- 
=========

 SELECT 
    COUNT([BadgeNumber]) as [Total_Number_Answered] ,     
    RespondedYear, 
    RespondedMonth,  
    Date_Process,
    Value,                      
    State   

    FROM TABLE_B

    GROUP BY 

    RespondedYear, 
    RespondedMonth,  
    Date_Process,
    Value,                      
    State

----两个表的输出:

Result of the TABLE_A
===================

YEAR     MONTH           Sent_Questions
=====   =======     =====================  
2017     03                 2
2017     02                 0   
2018     03                 0
2018     05                 2
2019     05                 2       
2020     07                 2
2020     06                 2

Result of the TABLE_B

========================

YEAR     MONTH      Total_Number_Answered     
=====   =======     =====================   
2017     03                 0                  
2017     02                 0                   
2018     03                 1                   
2018     05                 1                   
2019     05                 2               
2020     07                 1               
2020     06                 1

---这就是我需要的结果——预期结果:==============================

YEAR     MONTH      Total_Number_Answered     Sent_Questions
=====   =======     =====================    ===============
2017     03                 0                         2
2017     02                 0                         0 
2018     03                 1                         0
2018     05                 1                         2
2019     05                 2                         2     
2020     07                 1                         2
2020     06                 1                         2

下面是我一直坚持的一个查询:我希望得到与预期结果表相同的数字。我想以某种方式将表a和表b与年和月合并,而不丢失任何数据。我有麻烦了。
以下是我开始处理的查询:

SELECT 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
,CASE WHEN Recipient_num IN ('00001') THEN 'Billing_Transaction'
WHEN Recipient_num IN ('00002') THEN 'Online_Transaction'
END as [Category]
,COUNT(DISTINCT case when [S_Type_Type] = '1' THEN 'BadgeNum' ELSE NULL END) as [Sent_Questions]
,COUNT([BadgeNum]) as [NumberOfBadges]
t2.[Counts_Display]

FROM [TABLE_A] as t1

FULL OUTER JOIN 

(

SELECT 
COUNT ([BadgeNumber]) as [Counts_Display]
,LEFT([RespondedYear],4)+LEFT([RespondedMonth],2) as [CombinedDates]
,VALUE
,State
) as t2

ON (t2.[BadgeNumber] = t1.[BadgeNum]) 

and t2.[RespondedYear] = t1.[Year]
and t1.[RespondedMonth] = t2.[Month]

GROUP bY 
MOnth,
Year, 
BadgeNum,
Gender,
Date_Sent,
Type_Status,
Living_State,
S_Type_Type,
t2.[Counts_Display]

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题