---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[F_ID] int,[C_ID] int,[Title] varchar(5),[IsChild] int)
insert [tb]
select 1,14,20,'哈哈',1 union all
select 2,14,21,'无误',1 union all
select 3,20,1,'哈哈1',0 union all
select 4,20,2,'哈哈2',0 union all
select 5,21,3,'无误1',0 union all
select 6,21,4,'无误2',0
---查询---
select * from [tb]
order by (case isChild when 1 then c_id else f_id end),ischild desc---结果---
ID F_ID C_ID Title IsChild
----------- ----------- ----------- ----- -----------
1 14 20 哈哈 1
3 20 1 哈哈1 0
4 20 2 哈哈2 0
2 14 21 无误 1
5 21 3 无误1 0
6 21 4 无误2 0(6 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[F_ID] int,[C_ID] int,[Title] varchar(5),[IsChild] int)
insert [tb]
select 1,14,20,'哈哈',1 union all
select 2,14,21,'无误',1 union all
select 3,20,1,'哈哈1',0 union all
select 4,20,2,'哈哈2',0 union all
select 5,21,3,'无误1',0 union all
select 6,21,4,'无误2',0
---查询---
select * from [tb]
order by (case isChild when 1 then c_id else f_id end),ischild desc---结果---
ID F_ID C_ID Title IsChild
----------- ----------- ----------- ----- -----------
1 14 20 哈哈 1
3 20 1 哈哈1 0
4 20 2 哈哈2 0
2 14 21 无误 1
5 21 3 无误1 0
6 21 4 无误2 0(6 行受影响)
解决方案 »
- SQL事件跟踪的问题
- sql基本常识问题
- bcp 执行老是 提示 复制方向必须是 'in'、'out' 或 'format'。那位高手指教一下
- 为何在触发器中,前面没有启动一个事务,即没有写begin transcation,就可以结束事务呢?即写上rollback transcation呢?
- SQL触发器怎么导出??
- 请问各位大大 SQL要怎么获取当前日期啊。
- 好机器反而性能差?
- 请教~~~iis虚拟目录连接数据库的问题
- 满足下列要求,SQL语句如何写?
- 多谢大家的多次解答 又一个问题
- @@@@@@@@@@@ 通过进货记录及销售记录求库存的SQL语句 有什么问题??
- update触发器疑难问题
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[F_ID] int,[C_ID] int,[Title] varchar(5),[IsChild] int)
insert [tb]
select 1,14,20,'哈哈',1 union all
select 2,14,21,'无误',1 union all
select 3,20,1,'哈哈1',0 union all
select 4,20,2,'哈哈2',0 union all
select 5,21,3,'无误1',0 union all
select 6,21,4,'无误2',0 union allselect 7,18,20,'黑黑',1 union all
select 8,18,21,'又有',1 union all
select 9,28,1,'哈哈1',0 union all
select 10,28,2,'哈哈2',0 union all
select 11,29,3,'无误1',0 union all
select 12,30,4,'无误2',0 union all
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[F_ID] int,[C_ID] int,[Title] varchar(5),[IsChild] int)
insert [tb]
select 1,14,20,'哈哈',1 union all
select 2,14,21,'无误',1 union all
select 3,20,1,'哈哈1',0 union all
select 4,20,2,'哈哈2',0 union all
select 5,21,3,'无误1',0 union all
select 6,21,4,'无误2',0 union all
select 7,18,20,'黑黑',1 union all
select 8,18,21,'又有',1 union all
select 9,28,1,'哈哈1',0 union all
select 10,28,2,'哈哈2',0 union all
select 11,29,3,'无误1',0 union all
select 12,30,4,'无误2',0
---查询---
select * from [tb] t
where F_ID=14 or F_ID in(select C_ID from tb where F_ID=14)
order by (case isChild when 1 then c_id else f_id end),ischild desc---结果---
ID F_ID C_ID Title IsChild
----------- ----------- ----------- ----- -----------
1 14 20 哈哈 1
3 20 1 哈哈1 0
4 20 2 哈哈2 0
2 14 21 无误 1
5 21 3 无误1 0
6 21 4 无误2 0(6 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[F_ID] int,[C_ID] int,[Title] varchar(5),[IsChild] int)
insert [tb]
select 1,14,20,'哈哈',1 union all
select 2,14,21,'无误',1 union all
select 3,20,1,'哈哈1',0 union all
select 4,20,2,'哈哈2',0 union all
select 5,21,3,'无误1',0 union all
select 6,21,4,'无误2',0 union all
select 7,18,20,'黑黑',1 union all
select 8,18,21,'又有',1 union all
select 9,28,1,'哈哈1',0 union all
select 10,28,2,'哈哈2',0 union all
select 11,29,3,'无误1',0 union all
select 12,30,4,'无误2',0
---查询---
select * from [tb]
where F_ID=14 or F_ID in(select C_ID from tb where F_ID=14)
order by (case isChild when 1 then c_id else f_id end),ischild desc---结果---
ID F_ID C_ID Title IsChild
----------- ----------- ----------- ----- -----------
1 14 20 哈哈 1
3 20 1 哈哈1 0
4 20 2 哈哈2 0
2 14 21 无误 1
5 21 3 无误1 0
6 21 4 无误2 0(6 行受影响)