mysql - 按每张表相同字段排序2张表,mysql,经典asp
问题描述
我正在尝试从 2 个表中订购数据以显示在一个列表中,这也基于第三个表中的 id。
表 3 包含其他 2 个表的 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
任何指向正确方向的指针都非常感谢?谢谢
解决方案
这是我的尝试。
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 等语句,这样我们就不必输入它们了。
推荐阅读
- python - 在列表中的字符串末尾添加感叹号
- python - 如何从scrapy中的函数中获取数据
- azure - Azure 翻译 API 调用正在返回和 40100
- php - 认证问题/困惑
- sql - 如何按类别分隔 SQL 分区:“row_number() over (partition by ...”?
- apache-kafka - Kafka-connect,Bootstrap 代理断开连接
- outlook-2016 - 电子邮件未在 Microsoft Outlook 2016 中显示 google Roboto 字体
- java - 作为 Windows 服务运行时创建屏幕截图
- powershell - 如何在 VSCode 中禁用 Format Document 以排除一行
- ios - 在 Swift / iOS 中将数据存储在磁盘上