为datatable中的每一行选择COUNT(外置ID)

I have datable where I store my dailyContent selection. This is different for each day.

我有数据存储我的每日内容选择。每一天都是不同的。

When I select more then one day, then I need to return items, which are the same for all days.

当我选择超过一天，然后我需要返回物品，这些物品在所有的日子里都是一样的。

I have tried something like this:

我试过这样的方法:

SELECT * FROM
                        (
                             SELECT
                                ( -- For count
                                SELECT COUNT(recipeId) AS [Count]
                                FROM DailyContentDish
                                WHERE
                                    (
                                        (weekDayId=@mon OR @mon IS NULL) OR
                                        (weekDayId=@tue OR @tue IS NULL) OR
                                        (weekDayId=@wed OR @wed IS NULL) OR
                                        (weekDayId=@thu OR @thu IS NULL) OR
                                        (weekDayId=@fri OR @fri IS NULL) OR
                                        (weekDayId=@sat OR @sat IS NULL) OR
                                        (weekDayId=@sun OR @sun IS NULL) 
                                    ) 
                                    GROUP BY DailyContentDish.recipeId
                                ) AS cnt, 
                                -- End for count
                                DailyContentDish.dailyContentDishId,
                                DailyContentDish.recipeId AS Rec, 
                                title, 
                                150 AS calories, 
                                defaultSmallImagePath,
                                activePreparationTime + passivePreparationTime AS overallPreparationTime,
                                CAST(
                                    CASE WHEN EXISTS
                                        (
                                        SELECT  DailyContentDishFavourite.dailyContentDishFavouriteId
                                        FROM DailyContentDishFavourite 
                                        WHERE DailyContentDishFavourite.dailyContentDishId = DailyContentDish.dailyContentDishId
                                        )
                                    THEN 1 
                                    ELSE 0
                                END 
                                AS BIT) AS isFavouriteWhenGeneratingMenu


                                FROM DailyContentDish
                                LEFT OUTER JOIN
                                    RecipesTranslations ON RecipesTranslations.recipeId = DailyContentDish.recipeId
                                LEFT OUTER JOIN
                                    RecipeAdditionalInformation ON RecipeAdditionalInformation.recipeId = DailyContentDish.recipeId
                    WHERE
                    isEnabled = 1 AND
                    mealId=@mealId AND 
                        (       
                                weekDayId=@mon OR
                                weekDayId=@tue OR
                                weekDayId=@wed OR
                                weekDayId=@thu OR
                                weekDayId=@fri OR
                                weekDayId=@sat OR
                                weekDayId=@sun
                                )

                            ) p
                            WHERE p.cnt = @daysCount

The problem is, that nested select which should return count returns it for all rows, not just one entry (each row, that is). Since entries with recipeId are entered more then once I would like to know how many times are they entered.

问题是，嵌套的select应该返回count，它将返回所有行，而不仅仅是一个条目(即每一行)。因为有recipeId的条目会被多次输入，所以我想知道它们输入了多少次。

SELECT
                                ( -- For count
                                SELECT COUNT(recipeId) AS [Count]
                                FROM DailyContentDish
                                WHERE
                                    (
                                        (weekDayId=@mon OR @mon IS NULL) OR
                                        (weekDayId=@tue OR @tue IS NULL) OR
                                        (weekDayId=@wed OR @wed IS NULL) OR
                                        (weekDayId=@thu OR @thu IS NULL) OR
                                        (weekDayId=@fri OR @fri IS NULL) OR
                                        (weekDayId=@sat OR @sat IS NULL) OR
                                        (weekDayId=@sun OR @sun IS NULL) 
                                    )  AND Something should be here (I guess)
                                    GROUP BY DailyContentDish.recipeId
                                ) AS cnt, 
                                -- End for count

This part should return COUNT of entries for each row I select - but it retuns me COUNT of all entries.

这个部分应该返回我所选择的每一行的条目数——但是它会返回所有条目的数量。

Or should I take a different path with this. Any hint is greatly appreciated.

或者我应该走另一条路。任何提示都非常感谢。

I am using MS SQL server 2008

我正在使用MS SQL server 2008

EDIT: this is whole stored procedure:

编辑:这是整个存储过程:

        @userId int,
        @languageId int,
        @mealId int,
        @mon int,
        @tue int,
        @wed int,
        @thu int,
        @fri int,
        @sat int,
        @sun int,
        @orderBy nvarchar(2),
        @pageSize int,
        @startRowIndex int

AS
BEGIN

SET NOCOUNT ON;

DECLARE @daysCount int
SET @daysCount = 0
IF (@mon IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@tue IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@wed IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@thu IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@fri IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@sat IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END
IF (@sun IS NOT NULL)
    BEGIN
        SET @daysCount = @daysCount+1
    END


-- Insert statements for procedure here
    SELECT *
    FROM (
SELECT
    (
        SELECT [Count] = COUNT(recipeId) 
        FROM dbo.DailyContentDish d
        WHERE
        (
            ISNULL(@mon, weekDayId) = weekDayId OR
            ISNULL(@tue, weekDayId) = weekDayId OR
            ISNULL(@wed, weekDayId) = weekDayId OR
            ISNULL(@thu, weekDayId) = weekDayId OR
            ISNULL(@fri, weekDayId) = weekDayId OR
            ISNULL(@sat, weekDayId) = weekDayId OR
            ISNULL(@sun, weekDayId) = weekDayId 
        ) 
        GROUP BY d.recipeId
    ) AS cnt, 
    d.dailyContentDishId,
    d.recipeId AS Rec, 
    title, 
    150 AS calories, 
    defaultSmallImagePath,
    activePreparationTime + passivePreparationTime AS overallPreparationTime,
    CASE WHEN d2.dailyContentDishId IS NULL THEN 1 ELSE 0 END AS isFavouriteWhenGeneratingMenu
FROM dbo.DailyContentDish d
LEFT JOIN dbo.RecipesTranslations r ON r.recipeId = d.recipeId
LEFT JOIN dbo.RecipeAdditionalInformation t ON t.recipeId = d.recipeId
LEFT JOIN dbo.DailyContentDishFavourite d2 ON d.dailyContentDishId = d2.dailyContentDishId
WHERE isEnabled = 1 
    AND mealId = @mealId 
    AND (       
        weekDayId = @mon OR
        weekDayId = @tue OR
        weekDayId = @wed OR
        weekDayId = @thu OR
        weekDayId = @fri OR
        weekDayId = @sat OR
        weekDayId = @sun
    )
    ) p
    WHERE p.cnt = @daysCount

Edit1: I have uploaded diagram:

我上传了图:

Here is sample data (for meal Id = 1, this is also selected on form), explanation: - recipe with ID 125 will be present on Monday (weekDayId = 1) and Saturday (weekDayId = 7). So, this recipe must be returned if I select only Monday OR if I select only Saturday OR if I select Monday and Saturday. If I also select any other day then ithis record is not returned. - recipe with ID 105 must be returned when weekDays 1, 6 and 7 (Monday, Saturday, Sunday) are selected. Same as above, if any other day is selected then this record is not returned.

这是示例数据(餐Id = 1,这也是选择在形式),解释:- Id为125的配方将出席周一(weekDayId = 1)和周六(weekDayId = 7)。所以,这道菜必须返回如果我只选择星期一或者我只选择星期六或如果我选择星期一和星期六。如果我也选择其他任何一天，那么ithis记录不会返回。-当选择星期一、六、七(星期一、六、星期日)时，必须归还带有ID 105的食谱。与上面一样，如果选择了其他任何一天，则不返回此记录。

4 个解决方案

#1

Possible this help you -

这可能对你有所帮助

ALTER PROCEDURE dbo.usp_GetDailyContentDish

    @userId INT,
    @languageId INT,
    @mealId INT,
    @mon INT,
    @tue INT,
    @wed INT,
    @thu INT,
    @fri INT,
    @sat INT,
    @sun INT,
    @orderBy NVARCHAR(2),
    @pageSize INT,
    @startRowIndex INT

AS BEGIN

    SET NOCOUNT ON;

    DECLARE @daysCount INT
    SELECT @daysCount = 
        CASE WHEN @mon IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN @tue IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN @wed IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN @thu IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN @fri IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN @sat IS NOT NULL THEN 1 ELSE 0 END + 
        CASE WHEN @sun IS NOT NULL THEN 1 ELSE 0 END 

    SELECT
          Rec
        , calories
        , overallPreparationTime
        , isFavouriteWhenGeneratingMenu
    FROM (
        SELECT
            (
                SELECT [Count] = COUNT(d3.recipeId) 
                FROM dbo.DailyContentDish d3
                WHERE
                (
                    ISNULL(@mon, weekDayId) = weekDayId OR
                    ISNULL(@tue, weekDayId) = weekDayId OR
                    ISNULL(@wed, weekDayId) = weekDayId OR
                    ISNULL(@thu, weekDayId) = weekDayId OR
                    ISNULL(@fri, weekDayId) = weekDayId OR
                    ISNULL(@sat, weekDayId) = weekDayId OR
                    ISNULL(@sun, weekDayId) = weekDayId 
                ) AND d3.recipeId = d.recipeId 
                GROUP BY d3.recipeId
            ) AS cnt, 
            d.dailyContentDishId,
            d.recipeId AS Rec, 
            title, 
            150 AS calories, 
            defaultSmallImagePath,
            activePreparationTime + passivePreparationTime AS overallPreparationTime,
            CASE WHEN d2.dailyContentDishId IS NULL THEN 1 ELSE 0 END AS isFavouriteWhenGeneratingMenu
        FROM dbo.DailyContentDish d
        LEFT JOIN dbo.RecipesTranslations r ON r.recipeId = d.recipeId
        LEFT JOIN dbo.RecipeAdditionalInformation t ON t.recipeId = d.recipeId
        LEFT JOIN dbo.DailyContentDishFavourite d2 ON d.dailyContentDishId = d2.dailyContentDishId
        WHERE isEnabled = 1 
            AND mealId = @mealId 
            AND (       
                weekDayId = @mon OR
                weekDayId = @tue OR
                weekDayId = @wed OR
                weekDayId = @thu OR
                weekDayId = @fri OR
                weekDayId = @sat OR
                weekDayId = @sun
            )
    ) p
    WHERE p.cnt = @daysCount

END

Small off-top:

小离冠军:

This:

这样的:

AND (       
    weekDayId = @mon OR
    weekDayId = @tue OR
    weekDayId = @wed OR
    weekDayId = @thu OR
    weekDayId = @fri OR
    weekDayId = @sat OR
    weekDayId = @sun
)

Possible optimize to this:

可能的优化:

weekDayId % @weekDay = 0

If @mon, @tue is not null and contains 1, 2, ...

如果@mon， @tue不是null，并且包含1,2，…

#2

SELECT *
FROM (
      SELECT
        ( -- For count
         SELECT COUNT(d.recipeId) AS [Count]
         FROM DailyContentDish d
         WHERE (
                ISNULL(@amon, d.weekDayId) = d.weekDayId OR
                ISNULL(@tue, d.weekDayId) = d.weekDayId OR
                ISNULL(@wed, d.weekDayId) = d.weekDayId OR
                ISNULL(@thu, d.weekDayId) = d.weekDayId OR
                ISNULL(@fri, d.weekDayId) = d.weekDayId OR
                ISNULL(@sat, d.weekDayId) = d.weekDayId OR
                ISNULL(@sun, d.weekDayId) = d.weekDayId 
                ) AND d.recipeId = DailyContentDish.recipeId
          GROUP BY d.recipeId
          ) AS cnt, 
          -- End for count
          DailyContentDish.dailyContentDishId,
          DailyContentDish.recipeId AS Rec, 
          title, 
          150 AS calories, 
          defaultSmallImagePath,
          activePreparationTime + passivePreparationTime AS overallPreparationTime,
          CAST(
               CASE WHEN EXISTS
               (
                SELECT  DailyContentDishFavourite.dailyContentDishFavouriteId
                FROM DailyContentDishFavourite 
                WHERE DailyContentDishFavourite.dailyContentDishId = DailyContentDish.dailyContentDishId
                )
                    THEN 1 
                    ELSE 0
                END 
                AS BIT) AS isFavouriteWhenGeneratingMenu
         FROM DailyContentDish
           LEFT OUTER JOIN
             RecipesTranslations ON RecipesTranslations.recipeId = DailyContentDish.recipeId
           LEFT OUTER JOIN
             RecipeAdditionalInformation ON RecipeAdditionalInformation.recipeId = DailyContentDish.recipeId
         WHERE
           isEnabled = 1 AND
           mealId=@mealId AND 
             (       
              weekDayId=@mon OR
              weekDayId=@tue OR
              weekDayId=@wed OR
              weekDayId=@thu OR
              weekDayId=@fri OR
              weekDayId=@sat OR
              weekDayId=@sun
              )        
        ) p
WHERE p.cnt = @daysCount

UPDATE 21.05.2013(added Demo)

更新21.05.2013(演示)

IF OBJECT_ID('tempdb.dbo.#weekDays') IS NOT NULL DROP TABLE dbo.#weekDays     
SELECT weekDayId
INTO dbo.#weekDays
FROM(VALUES(@mon),
           (@tue),
           (@wed),
           (@thu),
           (@fri),
           (@sat),
           (@sun))x(weekDayId)
WHERE weekDayId IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX x ON dbo.#weekDays(weekDayId)

SELECT d.dailyContentDishId,
       d.recipeId AS Rec, 
       title, 
       150 AS calories, 
       defaultSmallImagePath,
       activePreparationTime + passivePreparationTime AS overallPreparationTime,
       CASE WHEN f.dailyContentDishId IS NULL THEN 1 ELSE 0 END AS isFavouriteWhenGeneratingMen
FROM DailyContentDish d
  LEFT JOIN RecipesTranslations ON RecipesTranslations.recipeId = d.recipeId
  LEFT JOIN RecipeAdditionalInformation ON RecipeAdditionalInformation.recipeId = d.recipeId
  LEFT JOIN dbo.DailyContentDishFavourite f ON d.dailyContentDishId = f.dailyContentDishId
WHERE d.isEnabled = 1 AND d.mealId = @mealId 
  AND NOT EXISTS(          
                 SELECT d3.weekDayId
                 FROM dbo.#weekDays d3
                 EXCEPT
                 SELECT d2.WeekDayId
                 FROM DailyContentDish d2
                 WHERE d.recipeId = d2.recipeId 
                   AND d2.isEnabled = 1 AND d2.mealId = @mealId 
                 )

Simple demo on SQLFiddle

简单的演示SQLFiddle

#3

only meals which are the same for that days (and meals) should be returned

只有当天相同的餐点(和餐点)才应退还

What is missing is the correlation of the outer query to the count calculation in your inner query. I.e. the following:

缺少的是外部查询与内部查询中的计数计算的相关性。如以下:

( DC1.WeekDayId In( @Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun )
    Or Coalesce( @Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun ) Is Null )
And DC1.MealId = DC.MealId

You need to alias at least one of the tables to make this happen. Typically, it is easier to read if you alias the tables on both the inner and outer query. The other item missing is that the seven variables for each day represent ticks ("select this day") instead of the day itself. If we change that, it makes the query simpler.

要实现这一点，至少需要对其中一个表进行别名。通常情况下，如果在内部和外部查询中使用表的别名，则更容易阅读。另一项缺少的是，每天的7个变量代表节拍(“选择今天”)，而不是一天本身。如果我们改变它，查询就会更简单。

Select @Mon = Case When @Mon Is Not Null Then 1 End
    , @Tue = Case When @Tue Is Not Null Then 2 End
    , @Wed = Case When @Wed Is Not Null Then 3 End
    , @Thu = Case When @Thu Is Not Null Then 4 End
    , @Fri = Case When @Fri Is Not Null Then 5 End
    , @Sat = Case When @Sat Is Not Null Then 6 End
    , @Sun = Case When @Sun Is Not Null Then 7 End;

 Select Count(*) Over() As CountOfResults
    ,   (
        Select Count(*)
        From DailyContentDish As DC1
        Where DC1.weekDayId = DC.weekDayId
        ) As CountOfDishesOnDay
    ,   (
        Select Count(Distinct mealId)
        From DailyContentDish As DC1
        Where DC1.weekDayId = DC.weekDayId
        ) As CountOfMeals           
    , DC.DailyContentDishId
    , DC.RecipeId As Rec
    , Title
    , 150 As Calories
    , DefaultsMallImagePath
    , ActivePreparationTime 
        + PassivePreparationTime As OverallPreparationTime
    , Cast  (
            Case 
            When Exists (
                        Select 1
                        From DailyContentDishFavourite As DF1
                        Where DF1.DailyContentDishId = DC.DailyContentDishId
                        ) Then 1 
            Else 0
            End 
            As Bit) As IsFavouriteWhenGeneratingMenu
From DailyContentDish As DC
    Left Join RecipesTranslations As RT
        On RT.RecipeId = DC.RecipeId
    Left Join RecipeAdditionalInformation As RA
        On RA.RecipeId = DC.RecipeId
Where DC.IsEnabled = 1 
    And DC.MealId=@MealId 
    And DC.WeekDayId In( @Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun )

Addition

除了

If the daily variables are supposed to represent a count of results per day returned (e.g., if @Mon = 2, then we should get back two rows for Monday), then you could do something like so:

如果每日变量应该表示每天返回的结果数(例如，如果@Mon = 2，那么我们应该在周一返回两行)，那么您可以这样做:

Declare @DailyParameters Table
    (
    DayCount int not null
    , DayOfWeek int not null
    )

Insert @DailyParameters( DayCount, DayOfWeek )
Select Z.Cnt, Z.DayOfWeek
From    (
        Select @Mon As Cnt, 1 As DayOfWeek
        Union All Select @Tue, 2
        Union All Select @Wed, 3
        Union All Select @Thu, 4
        Union All Select @Fri, 5
        Union All Select @Sat, 6
        Union All Select @Sun, 7
        ) As Z
Where Z.Cnt Is Not Null

The Where clause would then change to something like so:

Where子句会变成这样:

Where DC.IsEnabled = 1 
    And DC.MealId = @MealId 
    And DC.WeekDayId In( Select DayOfWeek From @DailyParameters )
    And (
        Select Count(*)
        From DailyContentDish As DC1
        Where DC1.weekDayId = DC.weekDayId
        ) = (
            Select D1.DayCount
            From @DailyParameters As D1
            Where D1.DayOfWeek = DC.weekDayId
            )

#4

"Since entries with recipeId are entered more then once I would like to know how many times are they entered."

“自从有了recipeId的条目之后，我想知道他们输入了多少次。”

Base on this statement I would just use the OVER clause with COUNT.

基于这个语句，我将使用带有COUNT的OVER子句。

Something like:

喜欢的东西:

SELECT
  COUNT(*) OVER (PARTITION BY recipeId) AS 'cnt'
  FROM DailyContentDish

I would need more details about the tables and what the output should be in order to provide more details.

为了提供更多的细节，我需要更多关于表的细节，以及输出应该是什么。

Here is more info on the OVER clause in 2008: http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx

这里有更多关于2008年条款的信息:http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx

Example using COUNT with OVER: http://blog.sqlauthority.com/2011/08/11/sql-server-tips-from-the-sql-joes-2-pros-development-series-advanced-aggregates-with-the-over-clause-day-11-of-35/

使用COUNT with OVER的示例:http://blog.sqlauthority.com/2011/08/11/sql-server-tips-from-the-sql-jo -2-pros-development- seriesadvanced aggregates-with- OVER -clause-day-11-of-35/