一句简单的查询语句

[HeyMystyle]

SELECT (SELECT COUNT(*)
          FROM seginfo
          WHERE holeid = a.holeid) AS segcount
FROM HoleInfo a
WHERE (segcount > 0)

提示segcoun列名无效怎么解决呢？

[王向飞]

COUNT(*)
as segcount

[lds1ove]

SELECT b.segcount
FROM HoleInfo a
apply (SELECT COUNT(*) segcount
          FROM seginfo
          WHERE holeid = a.holeid) b
WHERE (b.segcount > 0)

[HeyMystyle]

[Quote=引用 1 楼 wxf163 的回复:]
COUNT(*)
as segcount
[/Quote]你这句我还会呢，我省略了HoleInfo表里面的字段完整的是

SELECT HoleName,
          (SELECT COUNT(*)
         FROM seginfo
         WHERE holeid = a.holeid) AS segcount
FROM HoleInfo a
WHERE (segcount > 0)

[AcHerat]

SELECT (SELECT COUNT(*)
          FROM seginfo
          WHERE holeid = a.holeid) AS segcount
FROM HoleInfo a
WHERE (segcount > 0)

--这样查询的条件要在外边套一层
--楼主可以这样！
    select count(*) as segcount
    from HoleInfo a left join seginfo b on a.holeid = b.holeid
    group by a.holeid
    having count(*) > 0

[Spade_J]

SELECT HoleName,
          (SELECT COUNT(*)
         FROM seginfo
         WHERE holeid = a.holeid) AS segcount
FROM HoleInfo a
WHERE (segcount > 0)

WHERE (segcount > 0) 这句判断执行在标量子查询之前，所以你的那个AS segcount 不能用于前面的where

[HeyMystyle]

[Quote=引用 4 楼 acherat 的回复:]
SQL code

SELECT (SELECT COUNT(*)
FROM seginfo
WHERE holeid = a.holeid) AS segcount
FROM HoleInfo a
WHERE (segcount > 0)

--这样查询的条件要在外边套一层
--楼主可以这样！
select count(*) a……
[/Quote] 但是好像我查HoleInfo表里面的其他字段查不出来是怎么回事呢？

[sound02]

不能夠這樣用吧
你是不這樣的意思
表 tickets(badge,tick_no)

select badge,count(badge) from tickets group by badge having count(badge)>2

[HeyMystyle]

问题已经解决谢谢各位！

SELECT a.HoleID, a.HoleName, a.SiteName, COUNT(*) AS segcount
FROM HoleInfo a LEFT OUTER JOIN
      SegInfo b ON a.HoleID = b.HoleID
GROUP BY a.HoleID, a.HoleName, a.SiteName
HAVING (COUNT(*) > 0) AND (a.SiteName = 'AGL1')

这样就可以了