一个统计，SQL语句或存储过程要怎么实现！

Question

有4个表 a b c d
a是主表，b是a的从表，c是b的从表，d是个存标准值的表
大概结构
a
ID  GID  ……
——————————
b
ID  GID  a_GID  value  ……
——————————
c
ID  GID  b_GID  state  ……
——————————
d
ID  standard1  standard2 b表的a_GID存的是a表的GID，b表会有多个a_GID相同但value不同的记录，如下
a
ID  GID
1    1
2    2
3    3
————————
b
ID  GID  a_GID  value
1          1      50
2          2      51
3          2      45
4          1      43
5          3      57现在想做个统计，对a进行从大到小的排序，就是a表关联的b表value比d表standard1或standard2大的条数从大到小排序。
如果b的从表c的state是1，b的value就跟d的standard1比较，如果state是2就跟standard2比较
SQL语句或存储过程要怎么实现！
SQL语句或存储过程要怎么实现！

Answer 1

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')
BEGIN
DROP TABLE a
END
GO
CREATE TABLE a
(
ID INT,
GID INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'b')
BEGIN
DROP TABLE b
END
GO
CREATE TABLE b
(
ID INT,
GID INT,
a_GID INT,
value INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'c')
BEGIN
DROP TABLE c
END
GO
CREATE TABLE c
(
ID INT,
GID INT,
b_GID INT,
state INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'd')
BEGIN
DROP TABLE d
END
GO
CREATE TABLE d
(
ID INT,
standard1 INT,
standard2 INT
)
GO
INSERT INTO a
SELECT 1, 1 UNION
SELECT 2, 2 UNION
SELECT 3, 3INSERT INTO b
SELECT 1,1, 1, 50 UNION
SELECT 2,2, 2, 50 UNION
SELECT 3,3, 2, 51 UNION
SELECT 4,4, 1, 43 UNION
SELECT 5,5, 3, 57
INSERT INTO c
SELECT 1,1, 1, 1 UNION
SELECT 2,2, 2, 0 UNION
SELECT 3,3, 3, 1 UNION
SELECT 4,4, 4, 0 UNION
SELECT 5,5, 5, 1INSERT INTO d
SELECT 1,50,49
SELECT a.GID,COUNT(1) AS num
FROM a,b,c,d
WHERE a.GID = b.a_GID AND b.GID = c.b_GID AND value > CASE state WHEN 1 THEN standard1
 WHEN 0 THEN standard2 END
GROUP BY a.GID
ORDER BY num DESCGID num
2 2
3 1

Answer 2

问题就是a表下的记录对应着b表下的0~n条记录，b表下的记录会对应着c表下的0~1条记录，如果对应到的c表记录里的state是1的话b表的value就跟d表是standard1比较大小，如果c表没有对应的记录或state是2的话就跟standard2比较大小，如果b表的value大的话a表对应的那条记录就按b表大的越多的数量排序不知道明白吗。。
测试数据下面create table a
(
    ID varchar(10) primary key,
GID varchar(10)
)
create table b
(
    ID varchar(10) primary key,
GID varchar(10),
a_GID varchar(10),
[value] varchar(10)
)
create table c
(
    ID varchar(10) primary key,
GID varchar(10),
b_GID varchar(10),
state varchar(10)
)
create table d
(
    ID varchar(10) primary key,
standard1 varchar(10),
standard2 varchar(10)
)
insert into a (ID,GID) values('1','1')
insert into a (ID,GID) values('2','2')
insert into a (ID,GID) values('3','3')insert into b (ID,GID,a_GID,[value]) values('1','1','1','52')
insert into b (ID,GID,a_GID,[value]) values('2','2','2','51')
insert into b (ID,GID,a_GID,[value]) values('3','3','2','43')
insert into b (ID,GID,a_GID,[value]) values('4','4','1','44')
insert into b (ID,GID,a_GID,[value]) values('5','5','3','55')insert into c (ID,GID,b_GID,state) values('1','1','1','1')
insert into c (ID,GID,b_GID,state) values('2','2','2','1')
insert into c (ID,GID,b_GID,state) values('3','3','3','2')
insert into c (ID,GID,b_GID,state) values('4','4','4','1')
insert into c (ID,GID,b_GID,state) values('5','5','5','2')insert into d (ID,standard1,standard2) values('1','45','50')

Answer 3

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')
BEGIN
DROP TABLE a
END
GO
CREATE TABLE a
(
ID INT,
GID INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'b')
BEGIN
DROP TABLE b
END
GO
CREATE TABLE b
(
ID INT,
GID INT,
a_GID INT,
value INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'c')
BEGIN
DROP TABLE c
END
GO
CREATE TABLE c
(
ID INT,
GID INT,
b_GID INT,
state INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'd')
BEGIN
DROP TABLE d
END
GO
CREATE TABLE d
(
ID INT,
standard1 INT,
standard2 INT
)
GO
insert into a (ID,GID) values('1','1')
insert into a (ID,GID) values('2','2')
insert into a (ID,GID) values('3','3')insert into b (ID,GID,a_GID,[value]) values('1','1','1','52')
insert into b (ID,GID,a_GID,[value]) values('2','2','2','51')
insert into b (ID,GID,a_GID,[value]) values('3','3','2','43')
insert into b (ID,GID,a_GID,[value]) values('4','4','1','44')
insert into b (ID,GID,a_GID,[value]) values('5','5','3','55')insert into c (ID,GID,b_GID,state) values('1','1','1','1')
insert into c (ID,GID,b_GID,state) values('2','2','2','1')
insert into c (ID,GID,b_GID,state) values('3','3','3','2')
insert into c (ID,GID,b_GID,state) values('4','4','4','1')
insert into c (ID,GID,b_GID,state) values('5','5','5','2')insert into d (ID,standard1,standard2) values('1','45','50')
SELECT a.GID,COUNT(1) AS num
FROM a,b,c,d
WHERE a.GID = b.a_GID AND b.GID = c.b_GID AND value > CASE state WHEN 1 THEN standard1
 WHEN 2 THEN standard2 END
GROUP BY a.GID
ORDER BY num DESCGID num
1 1
2 1
3 1

Answer 4

表C中没有对应B的情况
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')
BEGIN
DROP TABLE a
END
GO
CREATE TABLE a
(
ID INT,
GID INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'b')
BEGIN
DROP TABLE b
END
GO
CREATE TABLE b
(
ID INT,
GID INT,
a_GID INT,
value INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'c')
BEGIN
DROP TABLE c
END
GO
CREATE TABLE c
(
ID INT,
GID INT,
b_GID INT,
state INT
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'd')
BEGIN
DROP TABLE d
END
GO
CREATE TABLE d
(
ID INT,
standard1 INT,
standard2 INT
)
GO
insert into a (ID,GID) values('1','1')
insert into a (ID,GID) values('2','2')
insert into a (ID,GID) values('3','3')insert into b (ID,GID,a_GID,[value]) values('1','1','1','52')
insert into b (ID,GID,a_GID,[value]) values('2','2','2','51')
insert into b (ID,GID,a_GID,[value]) values('3','3','2','43')
insert into b (ID,GID,a_GID,[value]) values('4','4','1','44')
insert into b (ID,GID,a_GID,[value]) values('5','5','3','55')insert into c (ID,GID,b_GID,state) values('1','1','1','1')
insert into c (ID,GID,b_GID,state) values('2','2','2','1')
insert into c (ID,GID,b_GID,state) values('3','3','3','2')
insert into c (ID,GID,b_GID,state) values('4','4','4','1')
--insert into c (ID,GID,b_GID,state) values('5','5','5','2')insert into d (ID,standard1,standard2) values('1','45','50')
SELECT a.GID,COUNT(1) AS num
FROM a INNER JOIN b ON a.GID = b.a_GID
   LEFT OUTER JOIN c ON b.GID = c.b_GID
   INNER JOIN d ON value > CASE state WHEN 1 THEN standard1
WHEN 2 THEN standard2
ELSE standard2 END
GROUP BY a.GID
ORDER BY num DESCGID num
1 1
2 1
3 1

Answer 5

magician547
这个能实现了好像！！但改下测试数据把b表的的第一条数据的value改成40，结果就不显示num为0的了，要把0的也显示出来要怎么写呢？

Answer 6

SELECT a.GID,SUM(CASE WHEN value > CASE state WHEN 1 THEN standard1
                                            WHEN 2 THEN standard2
                                            ELSE standard2 END THEN 1
                      ELSE 0 END) AS num
FROM a INNER JOIN b ON a.GID = b.a_GID
       LEFT OUTER JOIN c ON b.GID = c.b_GID
       INNER JOIN d ON 1 = 1
GROUP BY a.GID
ORDER BY num DESCGID num
2 1
3 1
1 0