求一SQL语句，关于本期相对于上期进出数

Question

机构持股情况表结构如下：股票代码 报表日期   机构代码
600050   20070331   1600001
600050   20070331   1600002
600050   20061231   1600001
.....其中日期为四个财务报表的时间(3.31 6.30 9.30 12.31)，我想判断的是本期相对于上期新增加了几家机构，又减少了几家机构，用一条SQL语句应该如何写？生成的结果应该如下：
股票代码 报表日期     新进家数  退出家数
600050   20070331     5         3

Answer 1

--没测试
select 股票代码,报表日期,count(_t.机构代码) as 新进数,count(_t2.机构代码) as 退出数
from tablename _t
full join tablename _t1 
    on _t.股票代码 = _t2.股票代码 
    and _t.机构代码 = _t2.机构代码
    and datediff(month,_t.报表日期,_t2.报表日期) = -3
group by 股票代码,报表日期

Answer 2

--楼上别名写错了
select _t.股票代码,_t.报表日期,count(_t.机构代码) as 新进数
       ,count(_t1.机构代码) as 退出数
from tablename _t
full join tablename _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期

Answer 3

select 
        股票代码,
        报表日期,
        (
         select count(*) 
         from 表名 
 where 股票代码=t.股票代码 and 报表日期=t.报表日期 
                and 机构代码 not in 
(select 机构代码 from 表名 where 股票代码=t.股票代码 and 报表日期=(select top 1 报表日期 from 表名 where 股票代码=t.股票代码 and 报表日期<'20070331' order by 报表日期 desc))) as 新增家数
,(
          select count(*) 
          from 表名 
          where 股票代码=t.股票代码 and 报表日期=(select top 1 报表日期 from 表名 where 股票代码=t.股票代码 and 报表日期<'20070331' order by 报表日期 desc)
                and 机构代码 not in (select 机构代码 from 表名 where 股票代码=t.股票代码 and 报表日期=t.报表日期) 
         ) as 退出家数
from 表名 as t
where 报表日期='20070331'
group by 股票代码,报表日期

Answer 4

--改改
select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null and _t1.机构代码 is null then 1 else 0 end) 
        as 新进数
  ,sum(case when _t.机构代码 is null and _t1.机构代码 is not null then 1 else 0 end)
        as 退出数
from tablename _t
full join tablename _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期

Answer 5

wangtiecheng
老大，我身边没环境，再帮我看看我写的对不对，还是有点怀疑。

Answer 6

你的结果NULL NULL 0 3
600050 20061231 2 0
600050 20070331 1 0

Answer 7

--再改改,加个having
select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null and _t1.机构代码 is null then 1 else 0 end) 
        as 新进数
  ,sum(case when _t.机构代码 is null and _t1.机构代码 is not null then 1 else 0 end)
        as 退出数
from tablename _t
full join tablename _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null

Answer 8

--源数据：股票代码 报表日期   机构代码
600050 20070331 1600001
600050 20070331 1600002
600050 20061231 1600001
600050 20061231 1600004--你的SQLselect _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null and _t1.机构代码 is null then 1 else 0 end) 
        as 新进数
  ,sum(case when _t.机构代码 is null and _t1.机构代码 is not null then 1 else 0 end)
        as 退出数
from 表名 _t
full join 表名 _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null--结果：
股票代码 报表日期     新进家数  退出家数
600050 20061231      2     0
600050 20070331      1     0

Answer 9

--再改改,再不对我就吐血了。把两个join条件写在where里面。select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null and _t1.机构代码 is null then 1 else 0 end) 
        as 新进数
  ,sum(case when _t.机构代码 is null and _t1.机构代码 is not null then 1 else 0 end)
        as 退出数
from tablename _t
full join tablename _t1 
       and _t.机构代码 = _t1.机构代码
where _t.股票代码 = _t1.股票代码 
      and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null

Answer 10

select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null and _t1.机构代码 is null then 1 else 0 end) 
        as 新进数
  ,sum(case when _t.机构代码 is null and _t1.机构代码 is not null then 1 else 0 end)
        as 退出数
from 表名 _t full join 表名 _t1 
       on _t.机构代码 = _t1.机构代码
where _t.股票代码 = _t1.股票代码 
      and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null
--结果：600050 20070331 0 0

Answer 11

--算了，我还是吐血吧，吐血之前再改改select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null 
             and _t.股票代码 is not null 
             and _t.报表日期 is not null 
             and _t1.机构代码 is null 
             and _t1.股票代码 is null 
             and _t1.报表日期 is null  
       then 1 else 0 end) as 新进数
  ,sum(case when _t.机构代码 is null 
             and _t.股票代码 is null 
             and _t.报表日期 is null 
             and _t1.机构代码 is not null 
             and _t1.股票代码 is not null 
             and _t1.报表日期 is not null  
       then 1 else 0 end) as 退出数
from 表名 _t
full join 表名 _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null

Answer 12

--原数据
600050 20070331 1600001
600050 20070331 1600002
600050 20061231 1600001
600050 20061231 1600004
select _t.股票代码,_t.报表日期
  ,sum(case when _t.机构代码 is not null 
             and _t.股票代码 is not null 
             and _t.报表日期 is not null 
             and _t1.机构代码 is null 
             and _t1.股票代码 is null 
             and _t1.报表日期 is null  
       then 1 else 0 end) as 新进数
  ,sum(case when _t.机构代码 is null 
             and _t.股票代码 is null 
             and _t.报表日期 is null 
             and _t1.机构代码 is not null 
             and _t1.股票代码 is not null 
             and _t1.报表日期 is not null  
       then 1 else 0 end) as 退出数
from 表名 _t
full join 表名 _t1 
    on _t.股票代码 = _t1.股票代码 
    and _t.机构代码 = _t1.机构代码
    and datediff(month,_t.报表日期,_t1.报表日期) = -3
group by _t.股票代码,_t.报表日期
having _t.股票代码 is not null and _t.报表日期 is not null
--select * from 表名--结果600050 20061231 2 0
600050 20070331 1 0

Answer 13

好像还是不对，连个full join都不会用，丢人啊。

Answer 14

--世上无难事，只要肯登攀……select isnull(_t1.股票代码,_t2.股票代码) as 股票代码
  ,isnull(_t1.报表日期,dateadd(month,3_t2.报表日期)) as 报表日期
  ,count(_t1.机构代码)-sum(case when _t1.机构代码 is not null 
         and _t2.机构代码 is not null then 1 else 0 end) as 新进数
  ,count(_t2.机构代码)-sum(case when _t1.机构代码 is not null 
         and _t2.机构代码 is not null then 1 else 0 end) as 退出数
from 表名 _t1
full join 表名 _t2 
    on _t1.股票代码 = _t2.股票代码 
    and _t1.机构代码 = _t2.机构代码
    and datediff(month,_t1.报表日期,_t2.报表日期) = -3
group by isnull(_t1.股票代码,_t2.股票代码)
     ,isnull(_t1.报表日期,dateadd(month,3_t2.报表日期))
having isnull(_t.报表日期,dateadd(month,3_t2.报表日期)) 
       <= (select max(报表日期) from 表名)

Answer 15

--楼上表的别名些错了，改改。
select isnull(_t1.股票代码,_t2.股票代码) as 股票代码
  ,isnull(_t1.报表日期,dateadd(month,3_t2.报表日期)) as 报表日期
  ,count(_t1.机构代码)-sum(case when _t1.机构代码 is not null 
         and _t2.机构代码 is not null then 1 else 0 end) as 新进数
  ,count(_t2.机构代码)-sum(case when _t1.机构代码 is not null 
         and _t2.机构代码 is not null then 1 else 0 end) as 退出数
from 表名 _t1
full join 表名 _t2 
    on _t1.股票代码 = _t2.股票代码 
    and _t1.机构代码 = _t2.机构代码
    and datediff(month,_t1.报表日期,_t2.报表日期) = -3
group by isnull(_t1.股票代码,_t2.股票代码)
      ,isnull(_t1.报表日期,dateadd(month,3_t2.报表日期))
having isnull(_t1.报表日期,dateadd(month,3_t2.报表日期)) 
       <= (select max(报表日期) from 表名)

Answer 16

我已测试过了，这下也知道full join该怎么用了，呵呵。

Answer 17

真是一个用full join的好例子：）

Answer 18

不错，我也学会了full join的用法。
只有经过自己的测试才能体会到精华。

Answer 19

象这样的功能最好不要用sql来实现，在应用程序里面实现比较好。
首先你说的本期不明确，你的意思是最后一期吧，但如果我需求变了，我想求上期与上上期之间的
变化呢？而且你新加家数和退出家数更加模糊，如果你是单指总的数量，那么只能是增加、减少、不变三种情况；如果你是从机构本身来看，有新的加入，也有老的退出，那么就要2期的机构比较才能得到。期数应该做为一个查询条件，可以通过应用程序输入得到。
select 股票代码 ,报表日期,机构代码
from   机构持股情况表
where  报表日期 = ？（上期，或者其他期） and 报表日期 = ? (本期 或者其他）
程序得到这些数据之后，再对每种股票进行分析，这种分析处理应该也是比较简单明了的：
[股票代码,报表日期,机构代码1,机构代码2,...,机构代码n]
把数据放入这样的一个结构之后再比较，不论你是想求总数的变化还是其他的变化都比较容易了。
这样sql就很简单了，数据库服务器负担应该越少越好，与业务相关的处理最好不要放在数据库服务器上执行。比较简单的没什么关系。这样不好的地方就是数据传输量可能会大一些，但程序的思路会非常的清晰，不会出现十分晦涩的sql语句。（上面的就很晦涩了，呵呵）