数据库表LOGIN_STAT 结构是
LOGIN_DAY,
SITE_ID,
LOGIN_COUNT,
REG_DT比如:
1.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090101',12,10,SYSDATE)
2.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090101',13,6,SYSDATE)
3.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090102',12,9,SYSDATE)
4.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090102',13,14,SYSDATE)现在希望用sql语句产生如下形式的数据格式LOGIN_DAY , SITE_ID 12 ,SITE_ID 13 ,TOTAL
20090101 10 6 22
20090102 9 14 22求教这个sql语句应该怎么写
LOGIN_DAY,
SITE_ID,
LOGIN_COUNT,
REG_DT比如:
1.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090101',12,10,SYSDATE)
2.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090101',13,6,SYSDATE)
3.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090102',12,9,SYSDATE)
4.INSERT INTO
LOGIN_STAT(LOGIN_DAY,SITE_ID,LOGIN_COUNT,REG_DATE)
VALUES
('20090102',13,14,SYSDATE)现在希望用sql语句产生如下形式的数据格式LOGIN_DAY , SITE_ID 12 ,SITE_ID 13 ,TOTAL
20090101 10 6 22
20090102 9 14 22求教这个sql语句应该怎么写
FROM
(
select LOGIN_DAY ,sum(case when SITE_ID=12 then LOGIN_COUNT else 0 end) SITE_ID_12
,sum(case when SITE_ID=12 then LOGIN_COUNT else 0 end) SITE_ID_13
from LOGIN_STAT
group by LOGIN_DAY
)
FROM
(
select LOGIN_DAY ,sum(case when SITE_ID=12 then LOGIN_COUNT else 0 end) SITE_ID_12
,sum(case when SITE_ID=13 then LOGIN_COUNT else 0 end) SITE_ID_13
from LOGIN_STAT
group by LOGIN_DAY
)
, sum(case when SITE_ID = 12 when LOGIN_COUNT else 0 end) SITE_ID12
, sum(case when SITE_ID = 13 when LOGIN_COUNT else 0 end) SITE_ID13
, sum(LOGIN_COUNT) TOTAL
from LOGIN_STAT
group by LOGIN_DAY;总和不是22吧