多选择的Sql（zend db select）

发布于 2024-10-15 20:27:11 字数 1013 浏览 2 评论 0原文

我需要一点帮助。我有（参考？）表，其中包含以下列： id 、 user_id 、 key 、 value

它几乎是一个用户配置文件表我想要 SQL （我正在使用 zend db 表，但一般 SQL 帮助就可以），我得到“所有 'user_id's，其中 'key' 是 somekey，'value' 是该 user_id 的某个值，但前提是它也匹配其中“key”是 otherkey，“value”是 othervalue”。

换句话说，我希望获得拥有耐克制造商且颜色为黑色的鞋子的用户。因此“key”是shoecolor，“value”是BLACK，并且具有相同user_id的另一行的“key”是shoemaker，“value”是NIKE。

这是我能想到的，但行不通。

SELECT `user_profiles`.* FROM `user_profiles` WHERE
(`key` = 'shoecolor' AND `value` = 'BLACK') AND
(`key` = 'shoemaker' AND `value` = 'NIKE')

如果有人了解 zend db：

$where = array('shoecolor' => 'BLACK', 'shoemaker' => 'NIKE');
    foreach ($where as $key => $value) {
        $sql = $db->quoteInto('key = ?', $key);
        $sql .= ' AND ' . $db->quoteInto('value = ?', $value);
        $select->where($sql);
    }
    // make unique result
    //$select->groupBy('user_id');
    $resultSet = $zendTableInstance->fetchAll($select);

请帮忙。谢谢。

原文

I need a bit of help.
I have (reference?) table with columns: id , user_id , key , value

It is pretty much a user profile table
and I would like to have SQL (I am using zend db table, but general SQL help will do) where I get "all 'user_id's where 'key' is somekey and 'value' is somevalue of that user_id but only if it also matches where 'key' is otherkey and 'value' is othervalue".

In other words I want to get users that have shoes of maker NIKE and color BLACK.
therefore 'key' is shoecolor and 'value' is BLACK and also another row with same user_id has 'key' is shoemaker and 'value' is NIKE.

This is what I could come up with, but doesn't work.

SELECT `user_profiles`.* FROM `user_profiles` WHERE
(`key` = 'shoecolor' AND `value` = 'BLACK') AND
(`key` = 'shoemaker' AND `value` = 'NIKE')

In case someone is knowledgable in zend db:

$where = array('shoecolor' => 'BLACK', 'shoemaker' => 'NIKE');
    foreach ($where as $key => $value) {
        $sql = $db->quoteInto('key = ?', $key);
        $sql .= ' AND ' . $db->quoteInto('value = ?', $value);
        $select->where($sql);
    }
    // make unique result
    //$select->groupBy('user_id');
    $resultSet = $zendTableInstance->fetchAll($select);

Please Help.
Thanx.

分享到QQ

分享到微博

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

发布评论

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

离鸿 2024-10-22 20:27:11

因为键/值对位于该行中，所以您的查询正在查找 3 AND 4 的键。值不能同时为 3 和 4 ;)

SELECT user_profiles.* FROM user_profiles WHERE (key = 3 AND value = 21) AND (key = 4 AND value = 55)

不起作用。

您可以自己加入并检查这些值吗？

SELECT user_profiles.* 
FROM user_profiles up1
  JOIN user_profiles up2 ON up1.user_id = up2.user_id
WHERE 
  (up1.key = 3 AND up1.value = 21) 
  AND (up2.key = 4 AND up2.value = 55)

Because the key/value pair is in the row, your query is looking for a key that is 3 AND 4. No value can be 3 and 4 at the same time ;)

SELECT user_profiles.* FROM user_profiles WHERE (key = 3 AND value = 21) AND (key = 4 AND value = 55)

will not work.

You could do a join on yourself, and check for these values?

SELECT user_profiles.* 
FROM user_profiles up1
  JOIN user_profiles up2 ON up1.user_id = up2.user_id
WHERE 
  (up1.key = 3 AND up1.value = 21) 
  AND (up2.key = 4 AND up2.value = 55)

回复收藏 0 原文

~没有更多了~