使用 UNION 将数据拉入网格 [英] Using UNION to pull data into grid

问题描述

我的网站上有一个数据网格，我从 2 个表中提取公司信息.

I have a data grid on my site where i'm pulling Company Information from 2 tables.

我正在创建一个表单来根据 3 个过滤器(类别、州、城市)对这些结果进行排序

I'm creating a form to sort these results based on 3 filters (Category,State,City)

这是我最初将数据加载到网格中的查询.我希望它填充我所有的表数据，直到用户决定过滤它，这就是为什么我使用 UNION 因为没有它我的查询正在等待我传递一个查询字符串值来完成它的 WHERE 语句.

This is my query to load data into the grid initially. I want it populated will ALL my table data UNTIL a user decides to filter it, which is why i'm using UNION as without it my query is waiting for me to pass a query-string value to complete it's WHERE statement.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID

UNION

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)

我不确定这是否是执行此操作的正确程序.

I'm not sure if this is the proper procedure to do this.

我无法理解此查询的 NULL 参数.在页面加载时没有查询字符串.

I'm having difficulties understanding the NULL parameter of this query. On page-load there is no query-string.

只有在用户按下排序"按钮后，才会将查询字符串添加到 URL 中.用户可以选择按 1 个或所有 3 个过滤器排序，但在这种情况下，他们只能按选项中的 1 个排序.查询字符串将为其他两个单独传递一个 ALL.

Only once a user presses the "SORT" button does a query-string get added into the URL. A user can choose to sort by 1 or all 3 of the filters but in the case they only sort by 1 of the options. The query-string will pass an ALL for the other two that were left alone.

IE: filter-Category/Barber%20Shop/filter-City/All/filter-State/All在这里，用户仅从 1 个下拉菜单中选择理发店"并离开 City &将下拉列表设为默认选项.

IE: filter-Category/Barber%20Shop/filter-City/All/filter-State/All Here a user only choose 'Barber Shop' from 1 drop-down and left the City & State drop downs as their default selections.

我正在侦听"查询字符串参数的查询在我的应用程序上无法正常工作.但是，在 SQL Management Studio 中使用以下内容时，它确实可以正确提取我的记录.

My query that is "listening" for the query-string parameters is not working correctly on my application. However when using the following in SQL Management studio it does pull my records properly.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = 'Barber Shop' OR '[querystring:filter-Category]' is      NULL)

这将是我的数据网格查询，其中包含所有 3 个侦听"WHERE 语句.

This would be my data-grid query with all 3 "Listening" WHERE statements included.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID

UNION

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)
AND (city = '[querystring:filter-City]' OR '[querystring:filter-City]' IS     NULL)
AND (state ='[querystring:filter-State]' OR '[querystring:filter-State]' IS     NULL)

非常感谢任何帮助、输入、示例、见解！

Any help, input, examples, insight highly appreciated!

在线示例&SQL 工作室

Examples Online & SQL Studio

推荐答案

Union 不会让你得到你想要的.目的是将 2 个或更多查询的结果连接在一起以保持不同的值.在您的情况下，联合的前半部分捕获所有数据，后半部分捕获过滤后的数据.由于上半场捕获了所有内容，因此所有内容都会返回.

Union isn't going to get you what you want. It's purpose it to join the results of 2 or more queries together keeping the distinct values. In your case the first half of the union captures all of the data and the second half captures the filtered data. Since first half captures all, all gets returned.

您这样做似乎是因为您在第一次运行时没有过滤器，但您想要在第二次运行时使用过滤器.

You appear to be doing this because you don't have filters for the first run but you want filters on the second run.

如果查询字符串不存在，则不要添加 where 子句.如果它们存在，则添加 where 和适当的子句.

Don't add the where clause if the query strings are not present. IF they are present, add where and the appropriate clauses.

您提到了排序，但您的查询缺少 order by 子句.如果您希望对数据进行排序，则需要添加一个.

You mention sort but your query lacks an order by clause. You'll want to add one if you want the data to be sorted.

第一次加载，没有 where 子句或所有过滤器都设置为ALL"

1st load, no where clause or all filters are set to 'ALL'

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
order by CategoryName, state, city

带有 where 子句的后续加载.

Subsequent loads, with where clauses.

SELECT * FROM BND_listing left join BND_ListingCategories
on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE (CategoryName = '[querystring:filter-Category]' OR     '[querystring:filter-Category]' is NULL)
AND (city = '[querystring:filter-City]' OR '[querystring:filter-City]' IS     NULL)
AND (state ='[querystring:filter-State]' OR '[querystring:filter-State]' IS     NULL)
order by CategoryName, state, city

您不能有 2 个查询，因为插件只允许 1 个.

You can't have 2 queries because the plugin only allows 1.

我假设聆听"是指 querystring:filter-Category 将被类别查询字符串中的任何内容替换.由于它在您的查询中被替换，因此您无法与 null 进行比较.由于没有过滤器是全部"，因此您需要考虑到这一点.试试这个:

I assume by "listening" you mean the querystring:filter-Category will be replaced by whatever is in the category query string. Since it's replaced in your query you can't compare to null. since no filter is 'ALL' you'll need to take that into account. Try this:

SELECT * 
FROM BND_listing 
left join BND_ListingCategories
    on BND_Listing.CatID=BND_ListingCategories.CatID
WHERE   
    (
        '[querystring:filter-Category]' = 'ALL' -- for when the queryString is set to ALL
        or '[querystring:filter-Category]' = '' -- for when the queryString isn't available like first load. this replaces the is null comparison. you can't compare a string literal to null as they are wrapped in quotes
        or CategoryName = '[querystring:filter-Category]' -- this is the comparison to your column
    )
    AND (
         '[querystring:filter-City]' = 'ALL'
        or '[querystring:filter-City]' = ''
        or city = '[querystring:filter-City]' 
    )
    AND (
        '[querystring:filter-State]'  = 'ALL'
        or '[querystring:filter-State]' = ''
        or state = '[querystring:filter-State]' 
    )
order by CategoryName, state, city

这篇关于使用 UNION 将数据拉入网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！