Select Distinct PROPERTY.PID As PID,
(Select Max(AREAMAX) From HOUSE Where HOUSE.PID = PROPERTY.PID) As MAXA,
PROPERTY.PNAME,
CITY.DIS,
From PROPERTY, HOUSE, CITY
Where PROPERTY.DID = CITY.DID
And PROPERTY.PID = HOUSE.PID
And CITY.CITY = 'Shanghai' And
Order By PNAME Asc,
DIS Asc,
PROPERTY.PID Desc,
' (Select Max(AREAMAX) From HOUSE Where HOUSE.PID = PROPERTY.PID) Desc
最后1句被注释掉的,,, 我加上它就提示语法错误了? 请问怎么 改?谢谢我是想实现 按 House 表中的 最大价格 倒序排序
(Select Max(AREAMAX) From HOUSE Where HOUSE.PID = PROPERTY.PID) As MAXA,
PROPERTY.PNAME,
CITY.DIS,
From PROPERTY, HOUSE, CITY
Where PROPERTY.DID = CITY.DID
And PROPERTY.PID = HOUSE.PID
And CITY.CITY = 'Shanghai' And
Order By PNAME Asc,
DIS Asc,
PROPERTY.PID Desc,
' (Select Max(AREAMAX) From HOUSE Where HOUSE.PID = PROPERTY.PID) Desc
最后1句被注释掉的,,, 我加上它就提示语法错误了? 请问怎么 改?谢谢我是想实现 按 House 表中的 最大价格 倒序排序
(Select Max(AREAMAX) From HOUSE Where HOUSE.PID = PROPERTY.PID) As MAXA,
PROPERTY.PNAME,
CITY.DIS,
From PROPERTY, HOUSE, CITY
Where PROPERTY.DID = CITY.DID
And PROPERTY.PID = HOUSE.PID
And CITY.CITY = 'Shanghai' )Order By PNAME Asc,
DIS Asc,
PROPERTY.PID Desc, MAXA
, AreaMax
, PROPERTY.PNAME
, CITY.DIS
FROM PROPERTY
INNER JOIN CITY ON PROPERTY.DID = CITY.DID
INNER JOIN HOUSE ON ROPERTY.PID = HOUSE.PID
WHERE CITY.CITY = 'Shanghai'
ORDER BY AreaMax DESC, PName ASC, PROPERTY.PID Desc
SELECT DISTINCT Propety.PID AS PID
, ISNULL(AreaMax, 0) AS AreaMax
, PROPERTY.PNAME
, CITY.DIS
FROM PROPERTY
INNER JOIN CITY ON PROPERTY.DID = CITY.DID
INNER JOIN HOUSE ON ROPERTY.PID = HOUSE.PID
LEFT JOIN (
SELECT MAX(AreaMax) AS AreaMax, PID
FROM HOUSE
GROUP BY PID
) AS TMP ON TMP.PID = ROPERTY.PID
WHERE CITY.CITY = 'Shanghai'
ORDER BY AreaMax DESC, PName ASC, PROPERTY.PID Desc
排序的功能.如下:City表结构:
did(自动编号) - cityname(城市名,如:上海市) - disname(城市下的区名,如:浦东新区)
其中did与 Property的 DID字段关联
Property表结构:
pid(自动编号) - did(区id,与city的did关联) - pname(物业名称)
House表结构:
hid(自动编号) - pid(物业ID,与property的pid关联) - maxarea(最大面积) - maxprice(最大价格)
其中: property的物业在house表中可能有多个记录(即价格).现在我要取的所有property的记录(且无重复),并且先按 物业名称的名称A-Z字母排序,再按价格从高到低排列,再按property.PID desc排序
由于每个物业对应的价格可能有多个,那么我就要从 house表中取出1条或多条记录中的最大价格,再排序.不知道,我的目的就是你写的效果?
等我测试好了.如果没什么问题,我再来结贴...
谢谢您的热情帮助!
如下图:我现在想实现:
读出city,property表中所有字段的值,及house表中对应pid字段的最大面积,最小面积,最大价格,最小面积;
且(property.pid)无重复记录;
再按照某1指定方式排序;另外:
按价格从高到低排序;
或按价格从低到高排序;
或按面积从高到低排序;
或按面积从低到高排序;
或按property.pid倒序排序;
也就是我得到的结果应该是:
按价格从高到低排序;
=================================================================================
pid - pname - maxarea - minarea - maxprice - minprice - cityname - disname
1 - 霞飞花苑 - 650 - 200 - 80000 - 20000 - shanghai - pudong
3 - 汤臣一品 - 300 - 180 - 60000 - 22000 - shanghai - pudong
2 - 万科红郡 - 800 - 380 - 55000 - 50000 - shanghai - pudong
按面积从高到低排序;
=================================================================================
pid - pname - maxarea - minarea - maxprice - minprice - cityname - disname
2 - 万科红郡 - 800 - 380 - 55000 - 50000 - shanghai - pudong
1 - 霞飞花苑 - 650 - 200 - 80000 - 20000 - shanghai - pudong
3 - 汤臣一品 - 300 - 180 - 60000 - 22000 - shanghai - pudong
按property.pid倒序排序;
=================================================================================
pid - pname - maxarea - minarea - maxprice - minprice - cityname - disname
1 - 霞飞花苑 - 650 - 200 - 80000 - 20000 - shanghai - pudong
2 - 万科红郡 - 800 - 380 - 55000 - 50000 - shanghai - pudong
3 - 汤臣一品 - 300 - 180 - 60000 - 22000 - shanghai - pudong