SQLSERVER7语句改造？(以前问过nononono大哥的问题，现在发现不行）

Question

我的要求是B表中同一个PRODUCT_NO的组合跟C表中不含PRODUCT_NO的组合比较，只要有一点不同就是不同，无论是多一个还是少一个或QTY不同都不行

Answer 1

select t1.product_no from 
  (select tb.product_no,count(tb.product_no) as fcount
       from tb left join tc on tb.part_no=tc.part_no and tb.qty=tc.qty
       group by tb.product_no
  ) t1,
  (select product_no,count(product_no) as fcount
       from tb  join tc on tb.part_no=tc.part_no and tb.qty=tc.qty
       group by product_no
  ) t2
where t1.product_no=t2.product_no and t1.fcount=t2.fcount 
  and t1.fcount=(select count(*) from tc)

Answer 2

试了一下。
还有一个问题。因为我在B表的part_no不是唯一的，就是一个物料在同一层可能会出现几次，而且QTY也是一样？那么当这种情况下，上面的语句就不行了：（

Answer 3

你是说可能会这样吗？
product_no , part_no , qty
100          1001      10
100          1001      10
100          1002      5
有点不可思议。

Answer 4

呵呵，按照数据库设计的方向来看，确实是很烂。我举个例子，是用户要求的：
产品A
        组分    RATING
物料1   10      *
物料2   5       *
物料3   6       **
物料1   10      **
就是这样，RATING的意义是那几个应该先混在一起，然后再几个RATING一起混合。
但是上述数据跟以下数据的意义是一样的：
        组分    RATING
物料1   10      **
物料2   5       **
物料3   6       *
物料1   10      *
所以才会有这样的问题。
JJ有什么好的建议呢

Answer 5

呵呵，按照数据库设计的方向来看，确实是很烂。我举个例子，是用户要求的：
产品A
        组分    RATING
物料1   10      *
物料2   5       *
物料3   6       **
物料1   10      **
就是这样，RATING的意义是那几个应该先混在一起，然后再几个RATING一起混合。
但是上述数据跟以下数据的意义是一样的：
        组分    RATING
物料1   10      **
物料2   5       **
物料3   6       *
物料1   10      *
所以才会有这样的问题。
JJ有什么好的建议呢

Answer 6

如果从设计的角度出发，我会将各个RATING的组合作为一个部件，然后产品A就指定使用了哪几个RATING就可以了。但是这样可能会出现很多层，当这样一棵树拿到工人面前时可能已经看昏了，所以才要求一定要在一个层次上表述清楚。如果确实还要深一层的东西的话，就是那些很简单的如：物料1|物料2 1:1这样的方式了

Answer 7

两个表都加一个rating字段，
select t1.product_no from 
  (select tb.product_no,count(tb.product_no) as fcount
      from tb left join tc on tb.part_no=tc.part_no and tb.qty=tc.qty and tb.rating=tc.rating
      group by tb.product_no
  ) t1,
  (select product_no,count(product_no) as fcount
      from tb  join tc on tb.part_no=tc.part_no and tb.qty=tc.qty and tb.rating=tc.rating
      group by product_no
  ) t2
where t1.product_no=t2.product_no and t1.fcount=t2.fcount 
  and t1.fcount=(select count(*) from tc)不知道这样可否？

Answer 8

不行啊，你没看到我上面给出的例子吗：
产品A
        组分    RATING
物料1  10      *
物料2  5      *
物料3  6      **
物料1  10      **
就是这样，RATING的意义是那几个应该先混在一起，然后再几个RATING一起混合。
但是上述数据跟以下数据的意义是一样的：
        组分    RATING
物料1  10      **
物料2  5      **
物料3  6      *
物料1  10      *
这个RATING只是让工人看靠哪几个应该先做好，然后在总混的。所以其实只是视觉作用，没有参考价值的

Answer 9

select identity(int,1,1) as id ,*  into #temp
from tb order by product_no,part_no,qtyselect identity(int,1,1) as id,* into #temp1
from tc order by part_no,qty
select * from #temp1select t1.product_no ,count(t1.product_no) as fcount
into #temp3
     from #temp t1 left join #temp1 t2 on t1.part_no=t2.part_no and t1.qty=t2.qty and 
        t1.id =t2.id+(select min(id) from #temp where product_no=t1.product_no)-1
     group by t1.product_no
select t3.product_no,count(t3.product_no) as fcount
into #temp4
     from #temp t3 join #temp1 t4 on t3.part_no=t4.part_no and t3.qty=t4.qty and 
        t3.id =t4.id+(select min(id) from #temp where product_no=t3.product_no)-1
     group by t3.product_no
select t5.product_no
from #temp3 t5,#temp4 t6
where t5.product_no=t6.product_no and t5.fcount=t6.fcount 
and t5.fcount=(select count(*) from tc)

Answer 10

后面三个语句本来我是写成一个：
select t5.product_no
from 
  (select t1.product_no ,count(t1.product_no) as fcount
     from #temp t1 left join #temp1 t2 on t1.part_no=t2.part_no and t1.qty=t2.qty and 
        t1.id =t2.id+(select min(id) from #temp where product_no=t1.product_no)-1
     group by t1.product_no
  ) t5,
  (select t3.product_no,count(t3.product_no) as fcount
     from #temp t3 join #temp1 t4 on t3.part_no=t4.part_no and t3.qty=t4.qty and 
        t3.id =t4.id+(select min(id) from #temp where product_no=t3.product_no)-1
     group by t3.product_no
  ) t6
where t5.product_no=t6.product_no and t5.fcount=t6.fcount 
and t5.fcount=(select count(*) from tc)结果SQL SERVER报错：
服务器: 消息 8624，级别 16，状态 13，行 1
内部 SQL Server 错误。难道SQL SERVER不支持这种写法？百思不得其解，只好分成三个语句来写了：-（ 看起来傻傻的：-（

Answer 11

啊，忘了告诉你，我本来已经有identity来做主键了。眼乱了，解释一下

Answer 12

原来要是有主键还是要这样倒一下。因为原来的主键可能不连续，用我上面的方法要求ID号连续，注意我用了order by product_no,part_no,qty 了的。有没有其他办法，我再想想：）

Answer 13

谢谢JJ，对了，刚才那个分解树的问题，请你到：
http://www.csdn.net/Expert/topic/481/481684.shtm
来领分。94分哪里就让给SEI大哥吧

Answer 14

解释：因为要完全满足条件，即表C中每一条记录在表B的product_no对应的记录中都存在，且没有不在表C中的记录存在。
转一个思路就是：两表左连接后的按product_no分组计数等于表C中的记录数（这样表示表B中没有不在表C中的记录），且两表内连接后按product_no分组计数也等于表C中的记录数（这样表示表C中记录在表B中都存在）。生成一个带自增长ID的临时表是为了连接时，part_no和qty相等的记录不至于被多次关联，每一条只关联一次，即关联成：
tB.id  tb.id  tb.part_no   tb.qty   tc.part_no    tc.qty
4      1      1001         10       1001          10
5      2      1001         10       1001          10
6      3      1002         5        1002          5        而不是：
tB.id  tb.id  tb.part_no   tb.qty   tc.part_no    tc.qty
4      1      1001         10       1001          10
4      2      1001         10       1001          10
5      1      1001         10       1001          10
5      2      1001         10       1001          10
6      3      1002         5        1002          5 所以用的是：
from #temp t1 left join #temp1 t2 on t1.part_no=t2.part_no and t1.qty=t2.qty and 
        t1.id =t2.id+(select min(id) from #temp where product_no=t1.product_no)-1       

Answer 15

另一个问题，你的方法没有测试成功，但为以后的扩展，还是采用SEI大哥的方法。
至于这个问题，不成功，数据如下
B表：
thisid               pro_no part_no part_name                      qty      rat
-------------------- ------ ------- ------------------------------ -------- ------ 
16                   00005  D016    8401                           10.00    1
17                   00005  D068    8501                           10.00    1
18                   00005  C035    CL-502                         20.00    2
19                   00005  B041    CRL                            5.00     2
C表
thisid               part_no part_name                      qty      rating 
-------------------- ------- ------------------------------ -------- ------ 
41                   D016    8401                           10.00    2
42                   D068    8501                           10.00    2
43                   B041    CRL                            5.00     1
44                   C035    CL-502                         20.00    1
理论上应该是应该返回与PRO_NO为00005的数据重复。
可以执行你的方法以后：(15 row(s) affected)
(4 row(s) affected)id          part_no qty      
----------- ------- -------- 
3           B041    5.00
4           C035    20.00
1           D016    10.00
2           D068    10.00(4 row(s) affected)
(5 row(s) affected)
(1 row(s) affected)pro_no 
------ (0 row(s) affected)

Answer 16

另外这个#temp产生的临时表会不会自动删除，如果我连续RUN两次就会出错。就算我DROP TABLE，其中有几个是不一定有的。听说有一中表是##temp

Answer 17

我没写上去，最后要用
drop table #temp
drop table #temp1
drop table #temp3
drop table #temp4
清掉临时表。我这里做结果是对的。我只写了五个语句，怎么出来这么多row(s) affected？至于另一个问题，若考虑层次问题，用通用的办法还是更好些。

Answer 18

临时表的问题也不大，我自己搞定了。
但是运行结果是我直接COPY出来的啊，就是不行啊

Answer 19

这个是语句，改了表结构
select identity(int,1,1) as id ,pro_no,part_no,qty into #temp
from pro_pu_bomb order by pro_no,part_no,qtyselect identity(int,1,1) as id,part_no,qty into #temp1
from pro_pu_bomc order by part_no,qty
select * from #temp1select t1.pro_no ,count(t1.pro_no) as fcount
into #temp3
    from #temp t1 left join #temp1 t2 on t1.part_no=t2.part_no and t1.qty=t2.qty and 
        t1.id =t2.id+(select min(id) from #temp where pro_no=t1.pro_no)-1
    group by t1.pro_no
select t3.pro_no,count(t3.pro_no) as fcount
into #temp4
    from #temp t3 join #temp1 t4 on t3.part_no=t4.part_no and t3.qty=t4.qty and 
        t3.id =t4.id+(select min(id) from #temp where pro_no=t3.pro_no)-1
    group by t3.pro_no
select t5.pro_no
from #temp3 t5,#temp4 t6
where t5.pro_no=t6.pro_no and t5.fcount=t6.fcount 
and t5.fcount=(select count(*) from pro_pu_bomc)
drop table #temp
drop table #temp1
drop table #temp3
drop table #temp4
结果是：(15 row(s) affected)
(4 row(s) affected)id          part_no qty      
----------- ------- -------- 
3           B041    5.00
4           C035    20.00
1           D016    10.00
2           D068    10.00(4 row(s) affected)
(5 row(s) affected)
(1 row(s) affected)pro_no 
------ (0 row(s) affected)

Answer 20

（所影响的行数为 14 行）
（所影响的行数为 4 行）id          part_no    qty         
----------- ---------- ----------- 
1           B041       5
2           C035       20
3           D016       10
4           D068       10（所影响的行数为 4 行）
（所影响的行数为 5 行）
（所影响的行数为 1 行）pro_no     
---------- 
00005（所影响的行数为 1 行）

Answer 21

奇怪奇怪。我将B表中某个PRO_NO下面的数据导进C表，然后运行，确实是可以得出结果。可我在我的程序中清空C表，然后插入跟B表中某个PRO_NO下面的数据，只是RATING特意反了。但就是不出来结果。什么原因呢？

Answer 22

语句中没有用rating字段啊？！我也晕了！·#￥%……—*（）决定睡一觉，明天再来：P

Answer 23

我的数据：
select * from pro_pu_bomb
select * from pro_pu_bomcpro_no     part_no    qty         rating     
---------- ---------- ----------- ---------- 
100        1001       10          NULL
100        1002       5           NULL
200        1001       10          NULL
200        1002       5           NULL
300        1001       10          NULL
300        1002       5           NULL
300        1003       5           NULL
400        1001       10          NULL
400        1002       10          NULL
100        1001       10          NULL
00005      D016       10          *
00005      D068       10          *
00005      B041       5           **
00005      C035       20          **（所影响的行数为 14 行）part_no    qty         rating     
---------- ----------- ---------- 
D016       10          **
D068       10          **
B041       5           *
C035       20          *（所影响的行数为 4 行）结果还是对的：-（我服了你了：-（

Answer 24

id          part_no qty      rating 
----------- ------- -------- ------ 
1           B041    5.00     1
2           C035    20.00    1
3           D016    10.00    2
4           D068    10.00    2(4 row(s) affected)
(6 row(s) affected)
(0 row(s) affected)pro_no 
------ (0 row(s) affected)
这个是我在应用程序里头插入以后到SQL运行的结果。
如果我不在程序中插入，而用SQL语句从B表中插入，结果是次次都对的。看：
id          part_no qty      
----------- ------- -------- 
4           B041    5.00
3           C035    20.00
1           D016    10.00
2           D068    10.00(4 row(s) affected)
(6 row(s) affected)
(1 row(s) affected)pro_no 
------ 
00005(1 row(s) affected)
看到其中的那段数据的不同吗？就是ID排的不一样。我每次在程序插入数据到C表以后运行都是按照1，2，3，4出来的，所以没有结果。如果我从B表中检索数据到C表，他的排序就是4，3，1，2。应该每次都这样。所以我说不行，你看是什么原因呢？
JJ可千万别睡，我已经做了几个通宵还在挺着呢。其他的模块也差不多了，就在这里困住了。明天还要交BETA呢。快帮我想想啊

Answer 25

是不是在C表中插入数据后没有GO一下？

Answer 26

我的程序中没有GO这个命令，倒是COMMIT了，你想想看，既然这段东西可以正确求出答案的话，就是我在程序中插入数据与在SQL QUERY里头搞进去有区别，就是这个区别导致这样的结果。如果这个ID能象SQL QUERY插入数据那样排就成功了。
我的程序只是向C表增加数据，测试都是在QUERY里头进行的

Answer 27

我的语句里用
select identity(int,1,1) as id,part_no,qty into #temp1
from pro_pu_bomc order by part_no,qty
select * from #temp1
应该是会重新生成一个正常的ID序号的，你在每个SQL语句后加一个
select * from ....看一下，看结果是不是正常？

Answer 28

加了以后发现一个问题。我在程序中插入数据的话，TEMP4会没有任何数据。如果在SQL QUERY里头插入的话就会有pro_no , fcount的数据出来。WHY？

Answer 29

哦，还有一点。就是在我没有加上：
drop table #temp
drop table #temp1
drop table #temp3
drop table #temp4
之前，在每次运行后我要运行第二次，就另外先
drop table #temp
drop table #temp1
drop table #temp3
drop table #temp4
当时时常会报错TEMP3不存在，或TEMP4不存在，出现的情况不一定，但肯定都出现过。因为我现在在每次运行后都DROP，所以就忘记曾经有这个问题。
是不是因为temp3或TEMP4不是每一次都可以创建成功？

Answer 30

这个是我在程序中插入的数据，可以看出一个TEMP表没有数据
(19 row(s) affected)id          pro_no part_no qty      
----------- ------ ------- -------- 
1           00001  B030    50.00
2           00001  B035    5.00
3           00002  C009    5.00
6           00002  C017    5.00
10          00003  00002   10.00
5           00003  B030    50.00
8           00003  B035    5.00
9           00003  B036    5.00
11          00003  C009    7.00
7           00004  00002   10.00
4           00004  B036    5.00
15          00005  B041    5.00
14          00005  C035    20.00
12          00005  D016    10.00
13          00005  D068    10.00
17          00006  D016    10.00
19          00006  D025    10.00
18          00006  D047    10.00
16          00006  D065    10.00(19 row(s) affected)
(4 row(s) affected)id          part_no qty      
----------- ------- -------- 
1           B041    5.00
2           C035    20.00
3           D016    10.00
4           D068    10.00(4 row(s) affected)
(6 row(s) affected)pro_no fcount      
------ ----------- 
00001  2
00002  2
00003  5
00004  2
00005  4
00006  4(6 row(s) affected)
(0 row(s) affected)pro_no fcount      
------ ----------- (0 row(s) affected)pro_no 
------ (0 row(s) affected)///////这个是在QUERY中插入数据的结果，可以看出，是一个TEMP表有了数据(19 row(s) affected)id          pro_no part_no qty      
----------- ------ ------- -------- 
1           00001  B030    50.00
2           00001  B035    5.00
3           00002  C009    5.00
6           00002  C017    5.00
10          00003  00002   10.00
5           00003  B030    50.00
8           00003  B035    5.00
9           00003  B036    5.00
11          00003  C009    7.00
7           00004  00002   10.00
4           00004  B036    5.00
15          00005  B041    5.00
14          00005  C035    20.00
12          00005  D016    10.00
13          00005  D068    10.00
17          00006  D016    10.00
19          00006  D025    10.00
18          00006  D047    10.00
16          00006  D065    10.00(19 row(s) affected)
(4 row(s) affected)id          part_no qty      
----------- ------- -------- 
4           B041    5.00
3           C035    20.00
1           D016    10.00
2           D068    10.00(4 row(s) affected)
(6 row(s) affected)pro_no fcount      
------ ----------- 
00001  2
00002  2
00003  5
00004  2
00005  4
00006  4(6 row(s) affected)
(1 row(s) affected)pro_no fcount      
------ ----------- 
00005  4(1 row(s) affected)pro_no 
------ 
00005(1 row(s) affected)

Answer 31

是的。不过你可以这样：在用into #temp前，先用create table #temp创建，这样总是存在#temp的，drop table #temp就不会报错了。

Answer 32

你是不是把前两个SQL语句的order by 取消了？看你的#temp和#temp1序号不对嘛！

Answer 33

TEMP和TEMP1的序号不对我早就说了，在上面。
ORDER BY还在
哦，B表是有PRO_NO字段而C表则没有，难道是因为这个原因造成

Answer 34

不行。我即使对每个表都ORDER BY 同一个也没用。就是TEMP4出不来。

Answer 35

你的 -1的根据是什么呢，会不会就是因为1-1 = 0 呢 ？如果RATING 是1，2，3，4。。那么还能不能用呢？

Answer 36

我怎么看#temp的序号也不对啊？我的是这样的：
（所影响的行数为 14 行）id          pro_no     part_no    qty         
----------- ---------- ---------- ----------- 
1           00005      B041       5
2           00005      C035       20
3           00005      D016       10
4           00005      D068       10
5           100        1001       10
6           100        1001       10
7           100        1002       5
8           200        1001       10
9           200        1002       5
10          300        1001       10
11          300        1002       5
12          300        1003       5
13          400        1001       10
14          400        1002       10（所影响的行数为 14 行）
（所影响的行数为 4 行）id          part_no    qty         
----------- ---------- ----------- 
1           B041       5
2           C035       20
3           D016       10
4           D068       10（所影响的行数为 4 行）
（所影响的行数为 5 行）pro_no     fcount      
---------- ----------- 
00005      4
100        3
200        2
300        3
400        2（所影响的行数为 5 行）
（所影响的行数为 1 行）pro_no     fcount      
---------- ----------- 
00005      4（所影响的行数为 1 行）pro_no     
---------- 
00005（所影响的行数为 1 行）pro_no     part_no    qty         rating     
---------- ---------- ----------- ---------- 
100        1001       10          NULL
100        1002       5           NULL
200        1001       10          NULL
200        1002       5           NULL
300        1001       10          NULL
300        1002       5           NULL
300        1003       5           NULL
400        1001       10          NULL
400        1002       10          NULL
100        1001       10          NULL
00005      D016       10          *
00005      D068       10          *
00005      B041       5           **
00005      C035       20          **（所影响的行数为 14 行）part_no    qty         rating     
---------- ----------- ---------- 
D016       10          **
D068       10          **
B041       5           *
C035       20          *（所影响的行数为 4 行）

Answer 37

呵呵，我看真的有问题。你试试在增加一行到B表和C表，数据随便，只要RATING是3，哈哈，结果你猜猜

Answer 38

什么“ -1”？？
指t3.id =t4.id+(select min(id) from #temp where pro_no=t3.pro_no)-1  ？
前面还有个t4.id呢！
与rating无关啊！

Answer 39

天啊，哪有这样的事情，不行就是不行嘛。你有NETMEETING吗，我把桌面让你控制一下你就知道了

Answer 40

那你总得找个方便的联络方法，例如MSN的也可以了