奇怪的问题。
我写了个视图,如下;
SELECT dbo.Houses.MianJi AS 面积, CASE WHEN Deals.SellID IN
(SELECT SellID
FROM Deals
GROUP BY SellID
HAVING (COUNT(SellID) = 1)) THEN '首次购房者 ' WHEN Deals.SellID IN
(SELECT SellID
FROM Deals
GROUP BY SellID
HAVING (COUNT(SellID) = 2)) THEN '2次交易者 ' WHEN Deals.SellID IN
(SELECT SellID
FROM Deals
GROUP BY SellID
HAVING (COUNT(SellID) > = 3 AND COUNT(SellID) < 5))
THEN '3-5次交易者 ' WHEN Deals.SellID IN
(SELECT SellID
FROM Deals
GROUP BY SellID
HAVING (COUNT(SellID) > = 5)) THEN '5次以上交易者 ' END AS 卖家属性,
dbo.HouseType.HouseTypeName AS 房型, dbo.Houses.BlockName AS 板块,
dbo.Houses.Qu AS 区, dbo.Deals.Price AS 单价, dbo.Deals.TotalPrice AS 总价,
CONVERT(varchar(4), dbo.Deals.CheckTime, 120) AS 年份, CONVERT(varchar(2),
dbo.Deals.CheckTime, 101) AS 月份, DATEDIFF(yy, dbo.Peoples.Birthday, GETDATE())
AS 年龄, dbo.Houses.FirstHandID AS 次数, dbo.Houses.XiangMu AS 项目
FROM dbo.Deals INNER JOIN
dbo.Houses ON dbo.Deals.HouseID = dbo.Houses.HouseID INNER JOIN
dbo.Peoples ON dbo.Deals.SellID = dbo.Peoples.PeopleID INNER JOIN
dbo.HouseType ON dbo.Houses.HouseTypeID = dbo.HouseType.TypeID
当我在存储过程里:
select 年龄 from View_SecondSellAttribute where 年份 = 2007 and 卖家属性= '2次交易者 '
把年份和卖家属性组合起来查询的时候就报超时,每个单独查询的时候就没错?
奇怪,大家看下什么地方有问题。
[解决办法]
学习
[解决办法]
那就换成函数试试。。
[解决办法]
当我在存储过程里:
select 年龄 from View_SecondSellAttribute where 年份 = 2007 and 卖家属性= '2次交易者 '
把年份和卖家属性组合起来查询的时候就报超时,每个单独查询的时候就没错?
奇怪,大家看下什么地方有问题。
直接在查询分析器里面运行语句看看是否超时?
select 年龄 from View_SecondSellAttribute where 年份 = 2007 and 卖家属性= '2次交易者 '
[解决办法]
--try the following view out first. It may help a little bit.
SELECT dbo.Houses.MianJi AS 面积, CASE WHEN DealType.No=1 THEN '首次购房者 '
WHEN DealType.No=2 THEN '2次交易者 '
WHEN DealType.No> =3 AND DealType.No <5 THEN '3-5次交易者 '
WHEN DealType.No> 5 THEN '5次以上交易者 ' END AS 卖家属性,
dbo.HouseType.HouseTypeName AS 房型, dbo.Houses.BlockName AS 板块,
dbo.Houses.Qu AS 区, dbo.Deals.Price AS 单价, dbo.Deals.TotalPrice AS 总价,
CONVERT(varchar(4), dbo.Deals.CheckTime, 120) AS 年份, CONVERT(varchar(2),
dbo.Deals.CheckTime, 101) AS 月份, DATEDIFF(yy, dbo.Peoples.Birthday, GETDATE())
AS 年龄, dbo.Houses.FirstHandID AS 次数, dbo.Houses.XiangMu AS 项目
FROM dbo.Deals INNER JOIN
(SELECT SellID, count(0) No FROM Deals GROUP BY SellID) DealType ON DealType.SellID=Deals.SellID INNER JOIN
dbo.Houses ON dbo.Deals.HouseID = dbo.Houses.HouseID INNER JOIN
dbo.Peoples ON dbo.Deals.SellID = dbo.Peoples.PeopleID INNER JOIN
dbo.HouseType ON dbo.Houses.HouseTypeID = dbo.HouseType.TypeID