Question

写一个SQl查询语句

写一个SQl查询语句(bus表)

Bus_no name
115 武林门
120 联庄
315 武林门
115 城站火车站
315 城站
120 城站火车站
808 联庄
808 武林门
问：有几种换乘方法可以从武林门到城站火车站，写SQL语句

急求
大哥门有没有简单点的啊

Answer 1

简单sql ，只能关联 已知的连接次数。(下面这个sql 假设导2次车，如果倒3次以上车在增加相应的UNION语句)
建议写一个函数，递归调用，得到多次乘车的换乘方法。网上也有一些例子也可以看下
百度文库http://wenku.baidu.com/view/f97ee821af45b307e8719712.html

select distinct a.name, a.bus_no, b.name, b.bus_no
from bus a, bus b
where a.bus_no = b.bus_no
and a.name = '武林门'
and b.name = '城站火车站'

union

select distinct a.name, a.bus_no, b.name, b.bus_no
from (select * from bus where bus.name = '武林门') a,
(select a.bus_no pre_no,
a.name a_name,
b.bus_no suss_no,
b.name b_name
from bus a, bus b
where a.name = b.name
and a.bus_no <> b.bus_no
and a.name<>'武林门'
and b.name<>'城站火车站') w,
(select * from bus b where b.name = '城站火车站') b
where a.bus_no = w.pre_no
and w.suss_no = b.bus_no
and a.bus_no <> b.bus_no
union

select distinct a.name, a.bus_no, b.name, b.bus_no
from (select * from bus where bus.name = '武林门') a,
(select a.bus_no pre_no,
a.name a_name,
b.bus_no suss_no,
b.name b_name
from bus a, bus b
where a.name = b.name
and a.bus_no <> b.bus_no
and a.name<>'武林门') w1,
(select a.bus_no pre_no,
a.name a_name,
b.bus_no suss_no,
b.name b_name
from bus a, bus b
where a.name = b.name
and a.bus_no <> b.bus_no
and a.name<>'武林门'
and b.name<>'城站火车站') w2,
(select * from bus b where b.name = '城站火车站') b
where a.bus_no = w1.pre_no
and w1.suss_no = w2.pre_no
and w2.suss_no = b.bus_no
and a.bus_no <> b.bus_no

Answer 2

---建立测试数据
create table bus
(bus_no varchar(10) null,
name varchar(50) null
)
go

--alter table bus drop CONSTRAINT PK__bus__60A75C0F
insert into bus
select '115','武林门'
union all
select '120','联庄'
union all
select '315','武林门'
union all
select '115','城站火车站'
union all
select '315','城站'
union all
select '120','城站火车站'
union all
select '808','联庄'
union all
select '808','武林门'
go

--将表旋转形成结果集：bus_no,qdz,zdz；然后查询出起点为‘武林门’，终点为‘城站火车站’的公交车并结果集保存到临时表#tb1
select c.qdz as 起始站点,c.bus_no as 公交路线,c.zdz as 终点站点
into #tb1
from
(
select a.bus_no,max(a.name) as qdz,max(b.name) as zdz
from bus a,bus b
where a.bus_no=b.bus_no and a.name<>b.name
group by a.bus_no,a.name,b.name
) c
where c.qdz='武林门' and c.zdz='城站火车站'

--查询出需换乘一次的公交路线，并将结果集保存到临时表#tb2
select q.aname as 起始站点,q.tbus_no 起始公交路线,q.bname as 中间站点,p.bus_no as 换乘公交路线,p.name as 终点站点
into #tb2
from bus p,
(
select w.bus_no as wbus_no,w.name as wname,t.bus_no as tbus_no,t.aname,t.bname
from bus w,
(
select a.bus_no,a.name as aname,b.name as bname from bus a,bus b
where a.bus_no=b.bus_no
and a.name='武林门'
and b.name <>'武林门'
and b.name<>'城站火车站'
) t
where w.bus_no<>t.bus_no and w.name=t.bname
) q
where p.bus_no=q.wbus_no and p.name='城站火车站' and p.name<>q.bname

--显示每种换乘方法并统计换乘方法个数
DECLARE @zd int
DECLARE @h1 int
DECLARE @sum varchar(100)
set @zd=(select count(*) from #tb1)
set @h1=(select count(*) from #tb2)
set @sum='共有'+CONVERT(varchar(10),@zd+@h1)+'种换乘方法可以从武林门到城站火车站'
select * from #tb1
select * from #tb2
select @sum

--删除测试数据
drop table #tb1
drop table #tb2
drop table bus

Answer 3

你试一下：

SELECT bus_no
FROM bus
WHERE name='武林门' or name='城站火车站'
GROUP BY bus_no
HAVING min(name)='城站火车站' and max(name)='武林门'
ORDER BY bus_no;

不行的话，把having 换成 min(name)='武林门' and max(name)='城站火车站' 再试试看，应该可以的。

Answer 4

LZ 是要每个企业都需显示5条，以下是SQLSERVER 写法能到达要求
--生成公司信息
CREATE TABLE Test_Company
(
FCompanyID int primary key identity(1,1),
FCompanyName nvarchar(300),
FCreateDate smalldatetime default( CONVERT(CHAR(10),Getdate(),121)) null
)
INSERT INTO Test_Company (FCompanyName)
SELECT '张三有限公司'
UNION ALL
SELECT '李四有限公司'
UNION ALL
SELECT '王五有限公司'
UNION ALL
SELECT '麻子有限公司'
--生成公司发布信息
CREATE TABLE Test_CompanyNews
(
FCompanyNewsID int primary key identity(1,1),
FCompanyID int constraint aaa foreign key (FCompanyID) references Test_Company(FCompanyID),
FNews nvarchar(4000),
FCreateDate datetime,
)
测试数据都不能写了 郁闷

最后就这样写
SELECT *
FROM Test_CompanyNews AS TA
WHERE FCompanyNewsID IN
(
SELECT top 5 FCompanyNewsID
FROM Test_CompanyNews AS TB
WHERE TA.FCompanyID = TB.FCompanyID
ORDER BY FCreateDate DESC
)

Answer 5

用一次内联啊，where Bus_no相等,将第二个表的name和Bus_no都改名字,然后select两个Bus_no,where name=武林门and name2=城站火车站,我忘了语言了,你自己写

Answer 6

SQL里的不等于应该用<>可能!=也行.没试过.
想知道你的那句对不对.要先知道state 和orderNum是什么类型的.如果是数值型 的不需要用单引号.

如果需要查询两天之隔
select * from order where state=1 and orderNum!='123' and date between start and end
另外,团IDC网上有许多产品团购,便宜有口碑