当前位置： 文江博客话题详情

MySQL 动态查询挑战 - 请帮忙！

发布于 2024-09-16 05:34:40 字数 2862 浏览 6 评论 0原文

我有这个查询：

   SELECT  
    userlist.USERID,  
    (case when (sum( CASE WHEN track.OFFER_ID = 221  THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211

FROM  
userlist  
INNER JOIN track ON userlist.USERID = track.USERID  

group by
userid

这是输出：

+------------+----------
| USERID     | offer_211 |
+------------+----------
| 1657487706 |         0 |  
| 1238439394 |         0 |  
| 1238427171 |         1 |  
| 1248431441 |         0 |  
| 1248464345 |         1 |

这个查询的目的：

有一个用户表、报价表和跟踪表。轨迹表包含用户表中的所有点击。我想创建一个像上面这样的视图，它将向我显示用户对所有优惠的所有点击。如果用户点击某个优惠，它将在该优惠列下显示“1”，否则将显示“0”。所有记录均来自轨迹表。

这是我需要帮助的内容： 我希望能够从“offer”表动态创建列“offer_211”，而不是手动指定它们。（请参阅我的查询）

因此，对于用户单击或未单击的所有优惠（列）将来自提供表。

它应该是这样的：“offer_”+offer.ID

它应该看起来像这样：（假设报价表中只有 2 个报价，ID 为 211, 212）

报价表中可能有 100 个报价，所以此视图应该有相同数量的动态列。

| USERID     | offer_211 | offer_212 |
+------------+----------+-------------
| 1657487706 |         0 |         1 |
| 1238439394 |         0 |         0 |
| 1238427171 |         1 |         0 |
| 1248431441 |         0 |         1 |
| 1248464345 |         1 |         0 |

track 表：

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int(22)     | NO   | PRI | NULL    | auto_increment |
| OFFER_ID   | int(22)     | YES  | MUL | NULL    |                |
| USERID     | int(22)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

userlist 表：

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| USERID      | int(22)      | NO   | PRI | 0       |       |
| EMAIL       | varchar(200) | YES  |     | NULL    |       |
| FIRSTNAME   | varchar(100) | YES  |     | NULL    |       |
| LASTNAME    | varchar(100) | YES  |     | NULL    |       |

offer 表：

+------------------+--------------+------+-----+-------------+----------------+
| Field            | Type         | Null | Key | Default     | Extra          |
+------------------+--------------+------+-----+-------------+----------------+
| ID               | int(11)      | NO   | PRI | NULL        | auto_increment |
| NAME             | varchar(100) | YES  |     | NULL        |                |
| DESCRIPTION      | text         | YES  |     | NULL        |                |
| URL              | text         | YES  |     | NULL        |                |
原文

I have this query:

   SELECT  
    userlist.USERID,  
    (case when (sum( CASE WHEN track.OFFER_ID = 221  THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211

FROM  
userlist  
INNER JOIN track ON userlist.USERID = track.USERID  

group by
userid

This is the output:

+------------+----------
| USERID     | offer_211 |
+------------+----------
| 1657487706 |         0 |  
| 1238439394 |         0 |  
| 1238427171 |         1 |  
| 1248431441 |         0 |  
| 1248464345 |         1 |

Purpose of this query:

There is a user table, offer table and track table. Track table contains all the clicks from user table. I want to create a view like above, which will show me all the clicks from users for all the offers. If the user clicked on a certain offer it will display '1' under that offer column, if not it will display '0'. All the records are coming from track table.

Here is what i need help with:
I want to be able to dynamically create columns "offer_211" from "offer" table instead of specifing them manually.(see my query)

So, for all the offers that a user clicked, or not clicked (columns) would be from offer the table.

it should be like this: "offer_"+offer.ID

It should look like this: (assuming there are only 2 offers in the offer table with ids 211, 212)

There could be 100's of offers in the offer table so there should be the same amount of dynamic columns for this view.

| USERID     | offer_211 | offer_212 |
+------------+----------+-------------
| 1657487706 |         0 |         1 |
| 1238439394 |         0 |         0 |
| 1238427171 |         1 |         0 |
| 1248431441 |         0 |         1 |
| 1248464345 |         1 |         0 |

track Table:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int(22)     | NO   | PRI | NULL    | auto_increment |
| OFFER_ID   | int(22)     | YES  | MUL | NULL    |                |
| USERID     | int(22)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

userlist Table:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| USERID      | int(22)      | NO   | PRI | 0       |       |
| EMAIL       | varchar(200) | YES  |     | NULL    |       |
| FIRSTNAME   | varchar(100) | YES  |     | NULL    |       |
| LASTNAME    | varchar(100) | YES  |     | NULL    |       |

offer Table:

+------------------+--------------+------+-----+-------------+----------------+
| Field            | Type         | Null | Key | Default     | Extra          |
+------------------+--------------+------+-----+-------------+----------------+
| ID               | int(11)      | NO   | PRI | NULL        | auto_increment |
| NAME             | varchar(100) | YES  |     | NULL        |                |
| DESCRIPTION      | text         | YES  |     | NULL        |                |
| URL              | text         | YES  |     | NULL        |                |

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

扫码二维码加入Web技术交流群

发布评论

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

评论（3）

狼性发作 2024-09-23 05:34:40

这种查询很快就会变得非常混乱。我建议使用更标准的查询来获取数据，然后在应用程序中处理该数据。

但是，如果确实有充分的理由创建动态查询，则可以采用以下一种方法 - 这些只是构建块，因此您需要对其进行修改以满足您的要求：

使用 CONCAT 和 GROUP_CONCAT 构建用于 准备好的语句：

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(offer_id = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT offer_id FROM track) AS track;

以下是上述命令创建的内容：

SELECT @sql;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT SUM(IF(offer_id = 1, 1, 0)) AS offer_1, SUM(IF(offer_id = 2, 1, 0)) AS offer_2, SUM(IF(offer_id = 3, 1, 0)) AS offer_3, SUM(IF(offer_id = 4, 1, 0)) AS offer_4 FROM track; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

创建并执行准备好的语句动态SQL：

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

This sort of query can become very messy, very quickly. I would suggest using more standard queries to obtain your data, then process that data in your application.

However, if there is a really good reason for creating a dynamic query, here's one way you can do it—these are just building blocks, so you'll need to modify it to suit your requirements:

Use CONCAT and GROUP_CONCAT to build a SELECT statement for use in a prepared statement:

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(offer_id = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT offer_id FROM track) AS track;

Here's what the above command creates:

SELECT @sql;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT SUM(IF(offer_id = 1, 1, 0)) AS offer_1, SUM(IF(offer_id = 2, 1, 0)) AS offer_2, SUM(IF(offer_id = 3, 1, 0)) AS offer_3, SUM(IF(offer_id = 4, 1, 0)) AS offer_4 FROM track; |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;
回复 原文
以可爱出名 2024-09-23 05:34:40

您是否考虑过获取用户 ID 和优惠 ID 的列表，首先按用户 ID 排序，然后按优惠 ID 排序？这是相同的信息，只是组织方式略有不同，而且数据库执行“select user_id, Offer_id from track”之类的查询会快得多。

Have you considered getting a list of user IDs and offer IDs, sorted first by user ID and next by offer ID? It's the same information, just organized slightly differently, plus it'd be much, much faster for the database to execute a query like "select user_id, offer_id from track".

回复 原文
高跟鞋的旋律 2024-09-23 05:34:40

迈克，

这是它返回的内容：看看最后它是如何被切断的 - 奇怪。可能是什么？

我也不明白你评论的最后一部分 - 最后一块；

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

这是新查询：

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(OFFER_ID = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT ID as OFFER_ID from offer where `STATUS`=1) AS track;

select @sql;

这是输出：

SELECT SUM(IF(OFFER_ID = 178, 1, 0)) AS Offer_178, SUM(IF(OFFER_ID = 234, 1, 0)) AS Offer_234, SUM(IF(OFFER_ID = 206, 1, 0)) AS Offer_206, SUM(IF(OFFER_ID = 213, 1, 0)) AS Offer_213, SUM(IF(OFFER_ID = 229, 1, 0)) AS Offer_229, SUM(IF(OFFER_ID = 220, 1, 0)) AS Offer_220, SUM(IF(OFFER_ID = 221, 1, 0)) AS Offer_221, SUM(IF(OFFER_ID = 222, 1, 0)) AS Offer_222, SUM(IF(OFFER_ID = 225, 1, 0)) AS Offer_225, SUM(IF(OFFER_ID = 226, 1, 0)) AS Offer_226, SUM(IF(OFFER_ID = 257, 1, 0)) AS Offer_257, SUM(IF(OFFER_ID = 259, 1, 0) ) AS Offer_259, SUM(IF(OFFER_ID = 258, 1, 0)) AS Offer_258, SUM(IF(OFFER_ID = 260, 1, 0)) AS Offer_260, SUM(IF(OFFER_ID = 228, 1, 0)) AS Offer_228, SUM(IF(OFFER_ID = 230, 1, 0)) AS Offer_230, SUM(IF(OFFER_ID = 232, 1, 0)) AS Offer_232, SUM(IF(OFFER_ID = 233, 1, 0)) AS Offer_233, SUM(IF(OFFER_ID = 239, 1, 0)) AS Offer_239, SUM(IF(OFFER_ID = 240, 1, 0)) AS Offer_240, SUM(IF(OFFER_ID = 241, 1, 0)) AS Offer_241, SUM( IF(OFFER_ID = 242, 1, 0)) AS Offer_242, SUM(IF(OFFER_ID = 243, 1, 0)) AS Offer_243, SUM(IF(OFFE FROM track;)

Mike,

Here what it returns: See at the end how its cut off - weird. What could it be?

I also didn't understand the last part of your comment - Last block;

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

Here is the new query:

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(OFFER_ID = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT ID as OFFER_ID from offer where `STATUS`=1) AS track;

select @sql;

Here is the output:

SELECT SUM(IF(OFFER_ID = 178, 1, 0)) AS offer_178, SUM(IF(OFFER_ID = 234, 1, 0)) AS offer_234, SUM(IF(OFFER_ID = 206, 1, 0)) AS offer_206, SUM(IF(OFFER_ID = 213, 1, 0)) AS offer_213, SUM(IF(OFFER_ID = 229, 1, 0)) AS offer_229, SUM(IF(OFFER_ID = 220, 1, 0)) AS offer_220, SUM(IF(OFFER_ID = 221, 1, 0)) AS offer_221, SUM(IF(OFFER_ID = 222, 1, 0)) AS offer_222, SUM(IF(OFFER_ID = 225, 1, 0)) AS offer_225, SUM(IF(OFFER_ID = 226, 1, 0)) AS offer_226, SUM(IF(OFFER_ID = 257, 1, 0)) AS offer_257, SUM(IF(OFFER_ID = 259, 1, 0)) AS offer_259, SUM(IF(OFFER_ID = 258, 1, 0)) AS offer_258, SUM(IF(OFFER_ID = 260, 1, 0)) AS offer_260, SUM(IF(OFFER_ID = 228, 1, 0)) AS offer_228, SUM(IF(OFFER_ID = 230, 1, 0)) AS offer_230, SUM(IF(OFFER_ID = 232, 1, 0)) AS offer_232, SUM(IF(OFFER_ID = 233, 1, 0)) AS offer_233, SUM(IF(OFFER_ID = 239, 1, 0)) AS offer_239, SUM(IF(OFFER_ID = 240, 1, 0)) AS offer_240, SUM(IF(OFFER_ID = 241, 1, 0)) AS offer_241, SUM(IF(OFFER_ID = 242, 1, 0)) AS offer_242, SUM(IF(OFFER_ID = 243, 1, 0)) AS offer_243, SUM(IF(OFFE FROM track;

回复 原文
~没有更多了~

关于作者

昔梦

暂无简介

0 文章
0 评论
20 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文