正在寻求帮助。我尝试按join、where和grp number的顺序运行代码(根据查询结果)。
我有两个测试表和文本:
create table test (
code varchar(10) primary key,
num int(10) not null,
name varchar(10) not null,
surname varchar(10) not null);
insert into test values (1,09,'Tom', 'Smith');
insert into test values (2,09,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');
insert into test values (8,15,'Adam','XYZ');
insert into test values (9,43,'John','Abc');
insert into test values (10,99,'Adam','Abc');
insert into test values (11,99,'John','Abc');
insert into test values (12,1,'A','A');
insert into test values (13,1,'J','A');
和表格文本:
create table text (
code varchar(10) primary key,
codeFK varchar(10) not null,
state varchar(10));
insert into text values (1,2,'O');
insert into text values (2,2,'O');
insert into text values (3,2,'O');
insert into text values (4,2,'C');
insert into text values (5,3,'O');
insert into text values (6,3,'O');
insert into text values (7,3,'O');
insert into text values (8,2,'O');
insert into text values (9,2,'C');
insert into text values (10,2,'O');
insert into text values (11,2,'C');
insert into text values (12,1,'C');
insert into text values (13,12,'C');
insert into text values (14,13,'C');
我加入他们并尝试按照以下代码添加coulm组:
加入-它工作
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
ORDER BY test.num DESC;
玻璃钢在其上的计算
select t.*,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp
from test t ORDER BY GRP;
但我的问题是如何用上面的连接查询得到grp列?因此,grp列将仅基于基于where语句的结果进行计算,而不是基于整个表测试。
我在下面试了一下,但它给了我一个错误:
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
AND test.num IN (select t.num,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp from test t)
ORDER BY test.num DESC;
预期结果:
2条答案
按热度按时间cgfeq70w1#
试试这个:
另一种更有效的方法:
ecfsfe2w2#
编辑:这应该指向正确的方向。而不是
AND
这应该是你选择的这是我的小提琴:http://sqlfiddle.com/#!2016年9月2日