如何在 mysql php 中构建动态运行时查询？

发布于 2024-11-08 11:37:00 字数 844 浏览 0 评论 0原文

我不知道如何分类这个问题。模糊地说，它是关于在使用 php 脚本执行的 mysql 查询的 WHERE 子句中使用计算值。

这是场景 - 我有一个 mysql 表，其结构如下： table_id[int], item_id[int], item_ rating[int] 现在 item_ rating 列中可以有“1”或“0”值。 table_id 列设置为 auto_increment，item_id 列也可以有重复值。

因此，一个典型的表格如下所示 -

table_id item_id item_rating
1           item1     1
2           item5     0
3           item1     1
4           item1     1
5           item5     1
6           item1     0

我打算为每个 item_id 做什么，我计算 item_ rating = 1 和 item_ rating = 0 的数量，然后取 item_ rating 值的差异以获得该项目的最终评级（final_item_ rating = item_ rating（值= 1）- item_ rating（值= 0））。

现在的问题是：我有一个 php 脚本，它从这些表中获取值，然后显示根据“final_item_ rating”值订购的项目详细信息 - 类似于： select * from table_name order by Final_item_Rating desc

唯一的问题是，由于这个 Final_item_Rating 本身不是列，并且实际上基于查询的运行时值，我如何构建查询？

希望我的问题清楚:)

原文

I do not know how to classify this question. Vaguely, its about using calculated value in the WHERE clause of a mysql query performed using a php script.

Here's the scenario -
I've a mysql table with structure like this: table_id[int], item_id[int], item_rating[int]
Now the item_rating column can have either a "1" or a "0" value in it. The table_id column is set to auto_increment and the item_id column can have duplicate values also.

So a typical table will look like below -

table_id item_id item_rating
1           item1     1
2           item5     0
3           item1     1
4           item1     1
5           item5     1
6           item1     0

What i intend to do i for each item_id, i count the number of item_rating = 1 and item_rating = 0 and then take the difference of item_rating values to get the final rating for that item (final_item_rating = item_rating(with value=1) - item_rating(with value=0) ).

Now the issue:
I have a php script that takes values from these tables, and then displays the item details ordered on the "final_item_rating" value - something like:
select * from table_name order by final_item_rating desc

only problem is, since this final_item_rating is not a column in itself, and is actually based on run time value of the query, how do i build a query?

hope i have the question clear :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

永言不败 2024-11-15 11:37:00

此查询可能会帮助您：

SELECT sum(item_rating) AS SumRatings
FROM table_name
WHERE item_rating=1
GROUP BY item_id
ORDER BY SumRatings;

This query may help you:

SELECT sum(item_rating) AS SumRatings
FROM table_name
WHERE item_rating=1
GROUP BY item_id
ORDER BY SumRatings;

回复收藏 0 原文

回忆凄美了谁 2024-11-15 11:37:00

此查询将对评级求和，并将最高评级的结果排序在顶部：

select  item_id
,       sum(case when item_rating = 1 then 1 else -1 end) as rating
from    YourTable
group by
        item_id
order by
        sum(case when item_rating = 1 then 1 else -1 end) desc

This query would sum the ratings, and order the result with the highest rating on top:

select  item_id
,       sum(case when item_rating = 1 then 1 else -1 end) as rating
from    YourTable
group by
        item_id
order by
        sum(case when item_rating = 1 then 1 else -1 end) desc

回复收藏 0 原文

~没有更多了~