连接where并添加计算列

ehxuflar 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(356)

正在寻求帮助。我尝试按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;

预期结果：

mysql

来源：https://stackoverflow.com/questions/51099558/join-where-and-add-calculated-column

2条答案

按热度按时间

cgfeq70w1#

试试这个：

select test.num, tt.cnt from test 
join text on test.code = text.codefk
join (
    select t1.num, count(*) cnt from (
        select distinct num
        from test ts1 
    join text tx1 on ts1.code = tx1.codefk
    where tx1.state = 'O'
) t1 join (
    select distinct num
    from test ts2
        join text tx2 on ts2.code = tx2.codefk
        where tx2.state = 'O'
    ) t2 on t1.num <= t2.num
    group by t1.num
) tt on test.num = tt.num
where text.state = 'O'

另一种更有效的方法：

select @lagNum := 0, @cnt := 0;
select num, cnt from (
    select case when num <> @lagNum then @cnt := @cnt + 1 end, @cnt cnt, @lagNum := num, num
    from (
        select (select num from test where code = t.codefk) num
        from text t
        where state = 'O'
    ) a order by num
) a

赞(0）回复(0）举报 2021-06-24

ecfsfe2w2#

编辑：这应该指向正确的方向。而不是 AND 这应该是你选择的
这是我的小提琴：http://sqlfiddle.com/#!2016年9月2日

select t.num,
   (select count(distinct t2.num) - 1
    from test t2
    where t2.num <= t.num
   ) as grp
from test t
LEFT JOIN text ON t.code = text.codeFK
WHERE text.state = 'O' 
ORDER BY t.num DESC;

赞(0）回复(0）举报 2021-06-24

我来回答

连接where并添加计算列

2条答案

相关问题

热门标签

最新问答