postgre sql 求助！急求大牛！

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

我想select 3 个 count （based on different conditions)  in one query
下面是我的code, 每个select单独run就对，加上union就run不出来了
Select datepart(y,mydate), datepart(mon, mydate), count(distinct link) as entire  From lemon
where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%'
group by datepart(y,mydate), datepart(mon,mydate)
order by datepart(y,mydate), datepart(mon,mydate)
Union
Select datepart(y,mydate), datepart(mon, mydate), count(distinct link) as Canada  From lemon
where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%'and countrycode = '164'
group by datepart(y,mydate), datepart(mon,mydate)
order by datepart(y,mydate), datepart(mon,mydate)
Union
Select datepart(y,mydate), datepart(mon, mydate), count(distinct link) as USA  From lemon
where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%'and countrycode = '840'
group by datepart(y,mydate), datepart(mon,mydate)
order by datepart(y,mydate), datepart(mon,mydate)

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：post POS GRE sql Confirmation SQL 求助

求助求助 求助 ！

我还写了另一个版本，可以run,但是结果不对。求高手指点啊

SELECT datepart(y,mydate), datepart(mon,mydate),
          (select count(distinct link) From lemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%' ) as entire ,
          (select count(distinct link) From lemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%'  and countrycode = '124') as Canada,
          (select count(distinct link) From lemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%'  and countrycode = '840') as USA
FROM lemon
group by datepart(y,mydate), datepart(mon,mydate)
order by datepart(y,mydate), datepart(mon,mydate)


date_part        date_part        entire        canada        usa
2014        1        10512        2065        7615
2014        2        10512        2065        7615
2014        3        10512        2065        7615
2014        4        10512        2065        7615
2014        5        10512        2065        7615
2014        6        10512        2065        7615
2014        7        10512        2065        7615
2014        8        10512        2065        7615
2014        9        10512        2065        7615
2014        10        10512        2065        7615
2014        11        10512        2065        7615
2014        12        10512        2065        7615
2015        1        10512        2065        7615
2015        2        10512        2065        7615
2015        3        10512        2065        7615
2015        4        10512        2065        7615
2015        5        10512        2065        7615
2015        6        10512        2065        7615
2015        7        10512        2065        7615
2015        8        10512        2065        7615
2015        9        10512        2065        7615

第三个版本 也是run不出来

select datepart(y,mydate), datepart(mon, mydate),
count(
select distinct link from lemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%';
) as entire,
count(
select distinct link from lemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%' and countrycode = '124';
) as CANADA,
count(
select distinct link fromlemon where link like '%shop.lemon.com/checkout/spk/confirmation.jsp%' and countrycode = '800';
) as USA
from lemon
group by datepart(y,mydate), datepart(mon,mydate)
order by datepart(y,mydate), datepart(mon,mydate)