如何在子查询中返回多个字段

[HeyMystyle]

SELECT HoleID, HoleName,
(SELECT COUNT(SegTo - SegFrom) ,waterRate
FROM SegInfo
WHERE holeid = a.holeid )
AS Expr1
FROM HoleInfo a


我要返回子查询中的COUNT(SegTo - SegFrom) 和waterRate
现在只能返回一个
能不能两个都返回啊？

[fpzgm]

SELECT HoleID, HoleName,
      (SELECT COUNT(SegTo - SegFrom) FROM SegInfo WHERE holeid = a.holeid ) AS Expr1,
      (SELECT waterRate FROM SegInfo WHERE holeid = a.holeid ) AS Expr2
FROM HoleInfo a

[fpzgm]

--或者把两个拼接起来返回
SELECT HoleID, HoleName,  
  (SELECT CAST(COUNT(SegTo - SegFrom) AS VARCHAR)+','+CAST(waterRate AS VARCHAR)
  FROM SegInfo
  WHERE holeid = a.holeid )  
  AS Expr1
FROM HoleInfo a

[HeyMystyle]

我现在就是用的
SELECT HoleID, HoleName,
(SELECT COUNT(SegTo - SegFrom) FROM SegInfo WHERE holeid = a.holeid ) AS Expr1,
(SELECT waterRate FROM SegInfo WHERE holeid = a.holeid ) AS Expr2
FROM HoleInfo a
我希望不要分开查询
第2种：
服务器: 消息 8118，级别 16，状态 1，行 1
列 'SegInfo.waterRate' 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。

[kevn]

SELECT HoleID, HoleName,  COUNT(SegTo - SegFrom) ,waterRate
FROM HoleInfo,SegInfo  WHERE holeid = a.holeid group by   HoleID, HoleName,  waterRate
??

[飘零一叶]

[Quote=引用 3 楼 heymystyle 的回复:]
我现在就是用的
SELECT HoleID, HoleName,
(SELECT COUNT(SegTo - SegFrom) FROM SegInfo WHERE holeid = a.holeid ) AS Expr1,
(SELECT waterRate FROM SegInfo WHERE holeid = a.holeid ) AS Expr2
FROM HoleInfo……
[/Quote]
waterRate 需要用聚合函数 具体看需求 sum（） 或者count（）

[juge001]

再写一个不就行了吗？为什么一定要返回到同一个字段里

[jiao3630]

[--小F--]

SELECT HoleID, HoleName,  
  (SELECT COUNT(SegTo - SegFrom) ,waterRate
  FROM SegInfo
  WHERE holeid = a.holeid group by waterRate
 )  
  AS Expr1
FROM HoleInfo a

[cxmcxm]

子查询中对应一个字段，不能返回两个字段的记录，要返回两字段，只能用两个子查询

SELECT HoleID, HoleName,  
  (SELECT COUNT(SegTo - SegFrom)  FROM SegInfo
  WHERE holeid = a.holeid )  
  AS Expr1，
  (SELECT waterRate
  FROM SegInfo
  WHERE holeid = a.holeid )  
  AS Expr2
 
FROM HoleInfo a

[dawugui]

[Quote=引用楼主 heymystyle 的回复:]
SELECT HoleID, HoleName,
(SELECT COUNT(SegTo - SegFrom) ,waterRate
FROM SegInfo
WHERE holeid = a.holeid )
AS Expr1
FROM HoleInfo a


我要返回子查询中的COUNT(SegTo - SegFrom) 和waterRate
现在只能返……
[/Quote]
没看懂你这句话的具体意思。

最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[Vidor]

-- 2005 / APPLY

SELECT a.HoleID, a.HoleName, Expr1.* FROM HoleInfo a OUTER APPLY
(
	SELECT COUNT(SegTo - SegFrom)cn, max(waterRate)waterRate
	FROM SegInfo
	WHERE holeid = a.holeid
) 
AS Expr1

-- 2000
select a.HoleID, a.HoleName, b.* from HoleInfo a
left join
(
	SELECT COUNT(SegTo - SegFrom)cn, max(waterRate)waterRate
	FROM SegInfo
	group by holeid
) b
on a.holeid=b.holeid

[obuntu]

学习，帮顶

[HeyMystyle]

你们说的都不行，我还是分开写吧！

[abuying]

SELECT HoleID, HoleName,  
  (SELECT COUNT(SegTo - SegFrom)  FROM SegInfo
  WHERE holeid = a.holeid )  
  AS Expr1,
  (SELECT top 1 waterRate
  FROM SegInfo
  WHERE holeid = a.holeid )  
  AS Expr2
FROM HoleInfo a