問題の説明:-2つのテーブルからレコードを取得します。1つは出席で、もう1つは特定のユーザーに関する特定の日付間の訪問です。
私は2つのテーブルを持っています1つは出席で2つ目は訪問です
1)出席
+-------------+------------+---------------------+
| user | date_today | in_time |
+-------------+------------+---------------------+
| MCCuser0036 | 25-02-2019 | 25-02-2019 10:59:08 |
| MCCuser0036 | 12-03-2019 | 12-03-2019 10:59:54 |
| MCCuser0036 | 14-03-2019 | 14-03-2019 11:01:11 |
| MCCuser0036 | 25-01-2019 | 25-01-2019 11:02:09 |
| MCCuser0036 | 25-03-2019 | 25-03-2019 11:03:10 |
| MCCuser0035 | 25-03-2019 | 25-03-2019 11:39:38 |
| MCCuser0036 | 26-03-2019 | 26-03-2019 11:30:47 |
| MCCuser0036 | 27-03-2019 | 27-03-2019 10:36:30 |
| MCCuser0036 | 28-03-2019 | 28-03-2019 17:18:01 |
| MCCuser0036 | 01-04-2019 | 01-04-2019 15:29:08 |
| MCCuser0036 | 08-04-2019 | 08-04-2019 23:01:57 |
| MCCuser007 | 26-04-2019 | 26-04-2019 20:04:36 |
| MCCuser0036 | 27-04-2019 | 27-04-2019 21:06:17 |
+-------------+------------+---------------------+
2)訪問
+-------------+------------+---------------------+
| username | add_time | visit_time_in |
+-------------+------------+---------------------+
| MCCuser0036 | 13-02-2019 | 13-02-2019 11:46:55 |
| MCCuser0036 | 14-02-2019 | 14-02-2019 11:47:04 |
| MCCuser0036 | 14-02-2019 | 14-02-2019 11:51:07 |
| MCCuser0036 | 14-02-2019 | 14-02-2019 11:51:30 |
| MCCuser0036 | 14-02-2019 | 14-02-2019 11:51:36 |
| MCCuser0036 | 18-02-2019 | 18-02-2019 15:09:42 |
| MCCuser0036 | 20-02-2019 | 20-02-2019 12:11:30 |
| MCCuser0036 | 25-02-2019 | 25-02-2019 13:05:42 |
| MCCuser0036 | 11-03-2019 | 11-03-2019 12:48:28 |
| MCCuser0036 | 01-04-2019 | 01-04-2019 15:55:45 |
| MCCuser007 | 26-04-2019 | 26-04-2019 20:04:55 |
| MCCuser007 | 26-04-2019 | 26-04-2019 20:05:10 |
| MCCuser0036 | 27-04-2019 | 27-04-2019 21:06:55 |
+-------------+------------+---------------------+
私が欲しいのは、特定のユーザーの特定の日付範囲での出席と訪問の記録です。
私の出力は、最初の出席、次にその日のそのユーザーのすべての訪問のようになります
+----------+------------------------+----------+---------------------+
|user |in_time |username |visit_time_in |
+----------+------------------------+----------+---------------------+
|given user|attendence of given date|given user|visits of given user |
+----------+------------------------+----------+---------------------+
私はこのクエリを使用していますが、空のセットと警告が表示され、mysqlと結合についての深い知識がないため、正しい方法でこれを行っているかどうかわかりません。
SELECT a.user,a.in_time,v.username,v.visit_time_in
FROM attendence AS a
INNER JOIN visits AS v ON a.user=v.username
WHERE a.user='MCCuser0036'
AND a.in_time BETWEEN STR_TO_DATE('01-04-2019', '%d-%m-%Y')
AND STR_TO_DATE('27-04-2019', '%d-%m-%Y')
ORDER BY a.in_time,v.visit_time_in DESC;
Empty SET, 22 WARNINGS (0.00 sec)
注:主キーと外部キーはなく、date_today、in_timeとadd_time、visit_time_inがあり、user、username列は両方ともvarcharです。
@SebastianBroschの更新された編集の答えは私が望んでいたものですが、DBにもっと多くの列があるので、基本構造を取得した後、それにいくつかの列を追加し、それをクエリします
SELECT * FROM (SELECT 'attendence' AS 'type', user AS 'username', STR_TO_DATE(in_time, '%d-%m-%Y %H:%i:%s') AS 'time_in', STR_TO_DATE(out_time, '%d-%m-%Y %H:%i:%s') AS 'time_out', location_in as 'place_in' FROM attendence UNION ALL SELECT 'visit', username, STR_TO_DATE(visit_time_in, '%d-%m-%Y %H:%i:%s'), STR_TO_DATE(visit_time_out, '%d-%m-%Y %H:%i:%s'), in_address FROM visits) t WHERE t.username = 'MCCuser0036' AND time_in BETWEEN STR_TO_DATE('01-01-2019', '%d-%m-%Y %H:%i:%s') AND STR_TO_DATE('27-04-2019', '%d-%m-%Y %H:%i:%s') ORDER BY time_in ASC;
しかし、それは私に予期しない結果を与えています
+------------+-------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| type | username | time_in | time_out | address_in | address_out |
+------------+-------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+
| attendence | MCCuser0036 | 2019-01-25 11:02:09 | 2019-01-25 11:02:10 | in_address | out_address |
| visit | MCCuser0036 | 2019-02-13 11:46:55 | 2019-02-13 11:47:00 | Sewri - Chembur Rd, Trombay Indusrial Area, Antop Hill, Mumbai, Maharashtra 400037, India | Sewri - Chembur Rd, Trombay Indusrial Area, Antop Hill, Mumbai, Maharashtra 400037, India |
| visit | MCCuser0036 | 2019-02-14 11:47:04 | 2019-02-14 11:47:10 | Sewri - Chembur Rd, Trombay Indusrial Area, Antop Hill, Mumbai, Maharashtra 400037, India | Sewri - Chembur Rd, Trombay Indusrial Area, Antop Hill, Mumbai, Maharashtra 400037, India |
| visit | MCCuser0036 | 2019-02-14 11:51:07 | 2019-02-14 11:51:10 | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India |
| visit | MCCuser0036 | 2019-02-14 11:51:30 | 2019-02-14 11:51:34 | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India |
| visit | MCCuser0036 | 2019-02-14 11:51:36 | 2019-02-14 11:51:39 | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India |
| visit | MCCuser0036 | 2019-02-18 15:09:42 | NULL | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India | |
| visit | MCCuser0036 | 2019-02-20 12:11:30 | 2019-02-20 13:44:36 | Sewri - Chembur Rd, Trombay Indusrial Area, Antop Hill, Mumbai, Maharashtra 400037, India | Building No 57, Gaurav Harmony, CGS Colony, Sector 7, Antop Hill, Mumbai, Maharashtra 400037, India |
| attendence | MCCuser0036 | 2019-02-25 10:59:08 | 2019-02-25 10:59:16 | in_address | out_address |
| visit | MCCuser0036 | 2019-02-25 13:05:42 | NULL | Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India | |
| visit | MCCuser0036 | 2019-03-11 12:48:28 | NULL | jay maharashNagar kokri agar S.M road anophill church Near By sayyed alam, musjid, Mumbai, Maharashtra 400037, India | |
| attendence | MCCuser0036 | 2019-03-12 10:59:54 | 2019-03-12 11:00:24 | in_address | out_address |
| attendence | MCCuser0036 | 2019-03-14 11:01:11 | 2019-03-14 11:01:28 | in_address | out_address |
| attendence | MCCuser0036 | 2019-03-25 11:03:10 | 2019-03-25 11:16:12 | in_address | out_address |
| attendence | MCCuser0036 | 2019-03-26 11:30:47 | NULL | in_address | out_address |
| attendence | MCCuser0036 | 2019-03-27 10:36:30 | 2019-03-27 10:49:05 | in_address | out_address |
| attendence | MCCuser0036 | 2019-03-28 17:18:01 | NULL | in_address | out_address |
| attendence | MCCuser0036 | 2019-04-01 15:29:08 | NULL | in_address | out_address |
| visit | MCCuser0036 | 2019-04-01 15:55:45 | NULL | 736, Aala Hazrat Rd, Jagruti Nagar, Nehru Nagar, Kurla East, Mumbai, Maharashtra 400071, India | |
| attendence | MCCuser0036 | 2019-04-11 15:17:56 | NULL | in_address | out_address |
| attendence | MCCuser0036 | 2019-04-23 17:41:05 | 2019-04-23 17:42:54 | in_address | out_address |
+------------+-------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+
誰かが私に理由を指摘できますか?
結果を得るSTR_TO_DATE
には、a.in_time
列でもを使用する必要があります。
SELECT a.user, a.in_time, v.username, v.visit_time_in
FROM attendence AS a INNER JOIN visits AS v ON a.user = v.username
WHERE a.user = 'MCCuser0036'
AND STR_TO_DATE(a.in_time, '%d-%m-%Y') BETWEEN STR_TO_DATE('01-04-2019', '%d-%m-%Y') AND STR_TO_DATE('27-04-2019', '%d-%m-%Y')
ORDER BY STR_TO_DATE(a.in_time, '%d-%m-%Y'), STR_TO_DATE(v.visit_time_in, '%d-%m-%Y') DESC;
は有効な日付値ではないため、条件a.in_time BETWEEN str_to_date('01-04-2019', '%d-%m-%Y') and str_to_date('27-04-2019', '%d-%m-%Y')
をtrueにできないため、結果は得られませんa.in_time
。また、STR_TO_DATE
onを使用してORDER BY
、レコードを日付値でソートする必要があります。
注:あなたが使用する必要があるDATETIME
列のデータ型in_time
、visit_time_in
およびDATE
上のデータ型add_time
とをdate_today
。したがって、STR_TO_DATE
最初に変換関数(のような)を使用せずにフィルタリングおよびソートできます。
1つのクエリで2つのテーブルの行のみを取得するには、次のUNION ALL
ように使用できます。
SELECT type, username, DATE_FORMAT(time_value, '%d-%m-%Y %H:%i:%s') AS 'time_value'
FROM (
SELECT 'attendence' AS 'type', `user` AS 'username', STR_TO_DATE(in_time, , '%d-%m-%Y') AS 'time_value'
FROM attendence
UNION ALL
SELECT 'visit', username, STR_TO_DATE(visit_time_in, '%d-%m-%Y')
FROM visits
) t
WHERE t.username = 'MCCuser0036'
AND time_value BETWEEN STR_TO_DATE('01-04-2019', '%d-%m-%Y') AND STR_TO_DATE('27-04-2019', '%d-%m-%Y')
ORDER BY type, time_value DESC
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加