很难的SQL

Question

今天注册的
SELECT city_id ,COUNT(user_id) as newregcount
SELECT DISTINCT user_id,CITY_ID,reg_time   FROM user WHERE quit_time IS NULL AND TO_DATE(reg_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
GROUP BY city_id 今天注销的
SELECT city_id ,COUNT(user_id) as newquitcount
SELECT DISTINCT  user_id,CITY_ID,reg_time   FROM user WHERE TO_DATE(QUIT_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD') and USER_ID not in (SELECT USER_ID FROM USER WHERE quit_time IS NULL )
group by 
city_id 

Answer 1

忘了加括号，你自己调试一下
SELECT city_id ,COUNT(user_id) as newregcount
(
SELECT DISTINCT user_id,CITY_ID,reg_time   FROM user WHERE quit_time IS NULL AND TO_DATE(reg_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
)
GROUP BY city_id SELECT city_id ,COUNT(user_id) as newquitcount(
SELECT DISTINCT  user_id,CITY_ID,reg_time   FROM user WHERE TO_DATE(QUIT_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD') and USER_ID not in (SELECT USER_ID FROM USER WHERE quit_time IS NULL ))
group by city_id

Answer 2

to yejianwind(啸风)
如果想让查询如下面的：
city_id    quit_count  reg_count
这样显示出来。应该怎么样联接你写的SQL？

Answer 3

SELECT A.city_id ,A.newregcount ,B.newquitcount 
FROM
(
SELECT city_id ,COUNT(user_id) as newregcount
(
SELECT DISTINCT user_id,CITY_ID,reg_time   FROM user WHERE quit_time IS NULL AND TO_DATE(reg_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
)
GROUP BY city_id 
)A,
(SELECT city_id ,COUNT(user_id) as newquitcount(
SELECT DISTINCT  user_id,CITY_ID,reg_time   FROM user WHERE TO_DATE(QUIT_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD') and USER_ID not in (SELECT USER_ID FROM USER WHERE quit_time IS NULL ))
group by city_id
B WHERE A.CITY_ID=B.CITY_ID

Answer 4

SELECT A.city_id ,A.newregcount ,B.newquitcount 
FROM
(
SELECT city_id ,COUNT(user_id) as newregcount
(
SELECT DISTINCT user_id,CITY_ID,reg_time   FROM user WHERE quit_time IS NULL AND TO_DATE(reg_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
)
GROUP BY city_id 
)A,
(SELECT city_id ,COUNT(user_id) as newquitcount(
SELECT DISTINCT  user_id,CITY_ID,reg_time   FROM user WHERE TO_DATE(QUIT_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD') and USER_ID not in (SELECT USER_ID FROM USER WHERE quit_time IS NULL ))
group by city_id)
B WHERE A.CITY_ID=B.CITY_ID

Answer 5

SELECT CITY_ID, COUNT(DISTINCT REGUSER), COUNT(DISTINCT QUITUSER)
  FROM (SELECT A.CITY_ID, A.USER_ID REGUSER, B.USER_ID QUITUSER
          FROM (SELECT *
                  FROM TEST3
                 WHERE TO_CHAR(REG_TIME, 'yyy-mm-dd') =
                       TO_CHAR(SYSDATE, 'yyy-mm-dd')
                   AND USER_ID NOT IN
                       (SELECT USER_ID
                          FROM TEST3
                         WHERE QUIT_TIME IS NULL
                           AND TO_CHAR(REG_TIME, 'yyy-mm-dd') <>
                               TO_CHAR(SYSDATE, 'yyy-mm-dd'))) A,
               
               (SELECT *
                  FROM TEST3
                 WHERE TO_CHAR(QUIT_TIME, 'yyy-mm-dd') =
                       TO_CHAR(SYSDATE, 'yyy-mm-dd')
                   AND USER_ID NOT IN
                       (SELECT USER_ID
                          FROM TEST3
                         WHERE QUIT_TIME IS NULL
                           AND TO_CHAR(REG_TIME, 'yyy-mm-dd') <>
                               TO_CHAR(SYSDATE, 'yyy-mm-dd'))) B
        
         WHERE A.USER_ID = B.USER_ID(+)
        UNION
        SELECT B.CITY_ID, A.USER_ID REGUSER, B.USER_ID QUITUSER
          FROM (SELECT *
                  FROM TEST3
                 WHERE TO_CHAR(REG_TIME, 'yyy-mm-dd') =
                       TO_CHAR(SYSDATE, 'yyy-mm-dd')
                   AND USER_ID NOT IN
                       (SELECT USER_ID
                          FROM TEST3
                         WHERE QUIT_TIME IS NULL
                           AND TO_CHAR(REG_TIME, 'yyy-mm-dd') <>
                               TO_CHAR(SYSDATE, 'yyy-mm-dd'))) A,
               (SELECT *
                  FROM TEST3
                 WHERE TO_CHAR(QUIT_TIME, 'yyy-mm-dd') =
                       TO_CHAR(SYSDATE, 'yyy-mm-dd')
                   AND USER_ID NOT IN
                       (SELECT USER_ID
                          FROM TEST3
                         WHERE QUIT_TIME IS NULL
                           AND TO_CHAR(REG_TIME, 'yyy-mm-dd') <>
                               TO_CHAR(SYSDATE, 'yyy-mm-dd'))) B
         WHERE A.USER_ID(+) = B.USER_ID)
 GROUP BY CITY_ID

Answer 6

SQL> CREATE TABLE test3
  2  (
  3  user_id varchar(20),
  4  city_id number,
  5  reg_time date,
  6  quit_time date
  7  )
  8  /Table createdSQL> select * from test3;USER_ID                 CITY_ID REG_TIME    QUIT_TIME
-------------------- ---------- ----------- -----------SQL> select * from test3;USER_ID                 CITY_ID REG_TIME    QUIT_TIME
-------------------- ---------- ----------- -----------
user1                         1 2006-2-1    
user2                         2 2006-2-4    2006-2-9
user3                         2 2006-2-9    2006-2-9
user1                         1 2006-2-9    2006-2-9
user4                         1 2006-2-9    
user5                         3 2006-2-9    2006-2-96 rows selected
执行如上sql，得到结果如下：1 1 0
2 1 2
3 1 1

Answer 7

修改一下前面的，因为连接条件可能不成立,SELECT city_id ,sum(newregcount) ,sum(newquitcount) 
  FROM
   (
      SELECT city_id ,COUNT(user_id) as newregcount,  0 newquitcount
        from
        (
        SELECT DISTINCT user_id,CITY_ID,reg_time
          FROM user WHERE quit_time IS NULL
           AND TO_DATE(reg_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
        )
        GROUP BY city_id 
   union all
    SELECT city_id ,0 newregcount,COUNT(user_id) as newquitcount
      from (
           SELECT DISTINCT  user_id,CITY_ID,reg_time 
             FROM user 
            WHERE TO_DATE(QUIT_time,'YYYY-MM-DD')=TO_DATE(SYSDATE,'YYYY-MM-DD')
              and USER_ID not in (SELECT USER_ID FROM USER WHERE quit_time IS NULL 
      )
      group by city_id
   )