如何将多个视图集合成一个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.车牌