如何将多个视图集合成一个SQL语句?

xiaoxiaoxiandan 2013-01-25 11:06:52
原来的公司项目需要视图,现在另外的系统需要使用到该数据,可是系统不可以使用视图,需要将视图集合成一个SQL语句,如何写呢?集合到展示这个视图上面。

----展示,这里是最后的视图,其他的2个视图在这个视图中有使用到

SELECT dbo.wuliu_v_car.车牌号, dbo.wuliu_v_car.电话, dbo.wuliu_v_park.省, dbo.wuliu_v_park.市, dbo.wuliu_v_park.第一次停车,
dbo.wuliu_v_park.最近一次停车, dbo.wuliu_v_park.累计停车次数, dbo.wuliu_v_park.[累计停车(分钟)], dbo.wuliu_v_park.累计停车费,
dbo.wuliu_v_park.[最长停车(分钟)], dbo.wuliu_v_car.车型, dbo.wuliu_v_car.[载重量(吨)], dbo.wuliu_v_car.[体积(立方)],
dbo.wuliu_v_car.[车长(米)], dbo.wuliu_v_car.[车宽(米)], dbo.wuliu_v_car.[车身高(米)]
FROM dbo.wuliu_v_car LEFT OUTER JOIN
dbo.wuliu_v_park ON dbo.wuliu_v_car.车牌号 = dbo.wuliu_v_park.车牌

----wuliu_v_car
SELECT dbo.wuliu_b_Organization.Name AS 停车场, dbo.wuliu_B_CAR.Name AS 车牌号, dbo.wuliu_B_CAR.UserManPhone AS 电话,
dbo.wuliu_b_CarType.Name AS 车型, dbo.wuliu_b_CarType.Weight AS [载重量(吨)], dbo.wuliu_b_CarType.Volume AS [体积(立方)],
dbo.wuliu_b_CarType.Length AS [车长(米)], dbo.wuliu_b_CarType.Width AS [车宽(米)], dbo.wuliu_b_CarType.Height AS [车身高(米)]
FROM dbo.wuliu_B_CAR LEFT OUTER JOIN
dbo.wuliu_b_CarType ON dbo.wuliu_B_CAR.CarTypeID = dbo.wuliu_b_CarType.InnerID LEFT OUTER JOIN
dbo.wuliu_b_Organization ON dbo.wuliu_B_CAR.OrganizationID = dbo.wuliu_b_Organization.InnerID

----wuliu_v_park
SELECT name AS 车牌, Province_name AS 省, City_name AS 市, MIN(InTime) AS 第一次停车, MAX(InTime) AS 最近一次停车, COUNT(InTime) AS 累计停车次数,
SUM(spanTime) AS [累计停车(分钟)], SUM(SpanPay) AS 累计停车费, MAX(spanTime) AS [最长停车(分钟)]
FROM dbo.wuliu_tab_ParkRecord AS I
GROUP BY name, Province_name, City_name


...全文
198 9 点赞 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
xiaoxiaoxiandan 2013-01-25
为什么数据不一样呢
  • 打赏
  • 举报
回复
快溜 2013-01-25
union all起来
  • 打赏
  • 举报
回复
我腫了 2013-01-25
引用 5 楼 xiaoxiaoxiandan 的回复:
3楼的代码查询的和视图查的不一样啊!
這不可能。
  • 打赏
  • 举报
回复
xiaoxiaoxiandan 2013-01-25
3楼的代码查询的和视图查的不一样啊!
  • 打赏
  • 举报
回复
叶子 2013-01-25
内容不一样的三个数据集?要放到一个数据集里面?

如果内容一样,可以union all
  • 打赏
  • 举报
回复
我腫了 2013-01-25
SELECT
        a.车牌号
       ,a.电话
       ,b.省
       ,b.市
       ,b.第一次停车
       ,b.最近一次停车
       ,b.累计停车次数
       ,b.[累计停车(分钟)]
       ,b.累计停车费
       ,b.[最长停车(分钟)]
       ,a.车型
       ,a.[载重量(吨)]
       ,a.[体积(立方)]
       ,a.[车长(米)]
       ,a.[车宽(米)]
       ,a.[车身高(米)]
    FROM
        (
			SELECT
			    dbo.wuliu_b_Organization.Name AS 停车场
			    ,dbo.wuliu_B_CAR.Name AS 车牌号
			    ,dbo.wuliu_B_CAR.UserManPhone AS 电话
			    ,dbo.wuliu_b_CarType.Name AS 车型
			    ,dbo.wuliu_b_CarType.Weight AS [载重量(吨)]
			    ,dbo.wuliu_b_CarType.Volume AS [体积(立方)]
			    ,dbo.wuliu_b_CarType.Length AS [车长(米)]
			    ,dbo.wuliu_b_CarType.Width AS [车宽(米)]
			    ,dbo.wuliu_b_CarType.Height AS [车身高(米)]
			FROM
				dbo.wuliu_B_CAR
				LEFT OUTER JOIN dbo.wuliu_b_CarType ON dbo.wuliu_B_CAR.CarTypeID = dbo.wuliu_b_CarType.InnerID
				LEFT OUTER JOIN dbo.wuliu_b_Organization ON dbo.wuliu_B_CAR.OrganizationID = dbo.wuliu_b_Organization.InnerID
		) AS a
        LEFT OUTER JOIN (
						SELECT
								name AS 车牌
							    ,Province_name AS 省
							    ,City_name AS 市
							    ,MIN(InTime) AS 第一次停车
							    ,MAX(InTime) AS 最近一次停车
							    ,COUNT(InTime) AS 累计停车次数
							    ,SUM(spanTime) AS [累计停车(分钟)]
							    ,SUM(SpanPay) AS 累计停车费
							    ,MAX(spanTime) AS [最长停车(分钟)]
							FROM
								dbo.wuliu_tab_ParkRecord AS I
							GROUP BY
								name
							   ,Province_name
							   ,City_name
					)AS b ON a.车牌号 = b.车牌
  • 打赏
  • 举报
回复
winner2050 2013-01-25
怎么你认为上面这些代码是视图,而不是sql代码?
  • 打赏
  • 举报
回复
xiaoxiaoxiandan 2013-01-25
那位大神帮我写下代码啊
  • 打赏
  • 举报
回复
xiaoxiaoxiandan 2013-01-25
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2013-01-25 11:06
社区公告
暂无公告