按每个表中的相同字段排序2个表，mysql，classic asp

kx5bkwkv 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(369)

我正试图根据第三个表的id，将两个表中的数据排序显示在一个列表中。
表3包含其他两个表的id。

e_allocation table
------------------
eid, sid, iid

5, 1234, NULL

5, NULL, 1234

表1和表2是类似数据的表格。

i_status table
--------------
iid, status

1234, Complete

1235, Complete

1236, Not Complete

s_status table
--------------
sid, status

1234, Complete

1235, Not Complete

1237, Not Complete

objCon.Execute ("SELECT a.sid, a.iid, b.status AS istats, c.status AS sstats
FROM e_allocation a LEFT JOIN i_status b ON a.iid=b.iid LEFT JOIN s_status c
ON a.sid=c.sid WHERE a.eid = '5' ORDER BY b.status Desc, c.status Desc")

因此，目前我可以从数据库中获取数据，但它显然先排序表b，然后再排序表c。我需要订购这些。
正在返回 (Table=I or S, Record ID=1234, Status = Complete or Not Complete) :

I:1234:Complete

I:1235:Complete

I:1236:Not complete

S:1234:Complete

S:1235:Not Complete

S:1237:Not Complete

我想得到：

I:1234:Complete

S:1234:Complete

I:1235:Complete

S:1235:Not Complete

I:1236:Not complete

S:1237:Not Complete

while not objDb.EOF
    sid = objDb("id")
    iid = objDb("iid")
    if sid <>"" then datlst = datlst &"S:"& sid &":"&objDb("sstats")&",<BR>"
    if iid <>"" then datlst = datlst &"I:"& iid &":"&objDb("istats")&",<BR>"
    objDb.MoveNext
Wend
response.write datlst

有没有任何指向正确方向的指示？谢谢

mysql asp-classic

来源：https://stackoverflow.com/questions/51599861/order-2-tables-by-the-same-field-in-each-table-mysql-classic-asp

1条答案

按热度按时间

kcrjzv8t1#

这是我的尝试。

create table e_allocation (eid int, sid int, iid int);
insert into e_allocation select 5, 1234, NULL;
insert into e_allocation select 5, NULL, 1234;
insert into e_allocation select 5, 1235, NULL;
insert into e_allocation select 5, NULL, 1235;
insert into e_allocation select 5, 1236, NULL;
insert into e_allocation select 5, NULL, 1236;

create table i_status (iid int, status varchar(25));
insert into i_status select 1234, 'Complete';
insert into i_status select 1235, 'Complete';
insert into i_status select 1236, 'Not Complete';

create table s_status (sid int, status varchar(25));
insert into s_status select 1234, 'Complete';
insert into s_status select 1235, 'Not Complete';
insert into s_status select 1237, 'Not Complete';

SELECT
  case when a.sid is null then 'I' else 'S' end as iors,
  ifnull(a.sid,a.iid) as id,
  ifnull(b.status,c.status) as status
FROM e_allocation a LEFT JOIN i_status b ON a.iid=b.iid LEFT JOIN s_status c
ON a.sid=c.sid
WHERE a.eid = '5' and (b.status is not null or c.status is not null)
ORDER BY id, iors

下次请提供create table等语句，这样我们就不必输入它们了。

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

我来回答

按每个表中的相同字段排序2个表，mysql，classic asp

1条答案

相关问题

热门标签

最新问答