想要计数,但每条记录重复1

麦克风

我想要一个计数,但每条记录都会重复1。你能建议做什么吗?

SELECT Count(*),
       innerTable.*
FROM   (SELECT (SELECT NAME
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS NAME,
               (SELECT url
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS Url,
               (SELECT TOP 1 NAME
                FROM   tourimages
                WHERE  tourimages.tourid = tourbooking.tourid
                ORDER  BY id ASC)                             AS ImageName,
               (SELECT duration + ' ' + CASE WHEN durationtype = 'd' THEN
                       'Day(s)' WHEN
                       durationtype =
                       'h' THEN 'Hour(s)' END
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS Duration,
               (SELECT Replace(Replace('<a> Adult(s) - <c> Children', '<a>', Sum
                               (CASE
                                       WHEN [type] = 1 THEN 1
                                       ELSE 0
                                END)),
                       '<c>',
                       Sum(CASE
                       WHEN [type] = 2 THEN 1
                       ELSE 0
                       END))
                FROM   tourperson
                WHERE  tourperson.bookingid = tourbooking.id) AS TotalPassengers
               ,
               startdate,
               createddate                                    AS BookingDate,
               id                                             AS BookingID,
               [status],
               serviceprice
        FROM   tourbooking
        WHERE  memberid = 6)AS innerTable
GROUP  BY innerTable.NAME,
          innerTable.bookingdate,
          innerTable.bookingid,
          innerTable.duration,
          innerTable.imagename,
          innerTable.serviceprice,
          innerTable.startdate,
          innerTable.status,
          innerTable.totalpassengers,
          innerTable.url 
托斯滕·凯特纳

您从中选择记录tourbooking您选择的列之一是id这可能是表的主键,因此是唯一的。(如果没有,您应该急于更改该名称。)

您将此ID称为BookingID,它是您进行分组的列之一。因此,您在中的每条记录得到一个结果记录tourbooking这样的“组”中的记录数当然是1;它是您选择并显示的一条记录。

如果您建立了真实的团体,说出每天的结果记录,那么您将获得真实的计数,例如每天的预订数量。

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

Related 相关文章

热门标签

归档