同一张表的两种情况的查询

[mianguiwu2107]

如题，我想要在tblcustomer表中查询两种情况的总count，
当id>53000时，查询条件是where (WarrantAccount1='483'
or WarrantAccount2='483')
and JoinDate>'2017-01-01 00:00:00.000'(作为count1)

当id<53000时查询条件为where (WarrantAccount1='483'
or WarrantAccount2='483')
and JoinDate>'2017-04-01 00:00:00.000'(作为count2)
求大神们解答

[二月十六]

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000' THEN 1
                 ELSE 0
            END) AS count1 ,
        SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000' THEN 1
                 ELSE 0
            END) AS count2
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

[二月十六]

上边那个少了一个id条件，试试这个

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) AS count1 ,
        SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                      AND id < 53000 THEN 1
                 ELSE 0
            END) AS count2
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

[小野马1209]

1楼正解，用CASE WHEN 解决 case when ... when ... else '' end as /*别名*/

[mianguiwu2107]

引用 2 楼 sinat_28984567 的回复:

上边那个少了一个id条件，试试这个

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) AS count1 ,
        SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                      AND id < 53000 THEN 1
                 ELSE 0
            END) AS count2
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

这个查出来的是两种情况的列，我要把他们相加得到总数

[二月十六]

引用 4 楼 mianguiwu2107 的回复:

[quote=引用 2 楼 sinat_28984567 的回复:]
上边那个少了一个id条件，试试这个

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) AS count1 ,
        SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                      AND id < 53000 THEN 1
                 ELSE 0
            END) AS count2
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

这个查出来的是两种情况的列，我要把他们相加得到总数[/quote]
那就加到一起……

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) + SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                                 AND id < 53000 THEN 1
                            ELSE 0
                       END) AS allcount
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

[mianguiwu2107]

引用 5 楼 sinat_28984567 的回复:

[quote=引用 4 楼 mianguiwu2107 的回复:] [quote=引用 2 楼 sinat_28984567 的回复:] 上边那个少了一个id条件，试试这个

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) AS count1 ,
        SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                      AND id < 53000 THEN 1
                 ELSE 0
            END) AS count2
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

这个查出来的是两种情况的列，我要把他们相加得到总数[/quote] 那就加到一起……

SELECT  SUM(CASE WHEN JoinDate > '2017-01-01 00:00:00.000'
                      AND id > 53000 THEN 1
                 ELSE 0
            END) + SUM(CASE WHEN JoinDate > '2017-04-01 00:00:00.000'
                                 AND id < 53000 THEN 1
                            ELSE 0
                       END) AS allcount
FROM    tblcustomer
WHERE   ( WarrantAccount1 = '483'
          OR WarrantAccount2 = '483'
        )

[/quote] OK、，多谢了