86 lines
1.8 KiB
Go
86 lines
1.8 KiB
Go
|
package test
|
||
|
|
|
||
|
|
import (
|
||
|
|
"context"
|
||
|
|
"fmt"
|
||
|
|
"testing"
|
||
|
|
|
||
|
|
"github.com/gogf/gf/v2/frame/g"
|
||
|
|
"github.com/samber/lo"
|
||
|
|
)
|
||
|
|
|
||
|
|
type DuplUser struct {
|
||
|
|
UserName string `json:"user_name"`
|
||
|
|
Phone string `json:"phone"`
|
||
|
|
Openid string `json:"openid"`
|
||
|
|
ID uint `json:"id"`
|
||
|
|
}
|
||
|
|
|
||
|
|
// 查询具有相同手机号的用户
|
||
|
|
func TestDuplUser(t *testing.T) {
|
||
|
|
// SELECT
|
||
|
|
//
|
||
|
|
// id,
|
||
|
|
// user_name,
|
||
|
|
// phone,
|
||
|
|
// openid
|
||
|
|
//
|
||
|
|
// FROM
|
||
|
|
//
|
||
|
|
// bus_construction_user
|
||
|
|
//
|
||
|
|
// WHERE
|
||
|
|
//
|
||
|
|
// phone IN (
|
||
|
|
// SELECT
|
||
|
|
// phone
|
||
|
|
// FROM
|
||
|
|
// bus_construction_user
|
||
|
|
// where
|
||
|
|
// deleted_at is nullAND phone <> ''
|
||
|
|
// GROUP BY
|
||
|
|
// phone
|
||
|
|
// HAVING
|
||
|
|
// COUNT(phone) > 1
|
||
|
|
// )
|
||
|
|
// AND phone IS NOT NULL AND phone <> ''
|
||
|
|
//
|
||
|
|
// ORDER BY phone;
|
||
|
|
var duplUsers []DuplUser
|
||
|
|
// 查询具有相同手机号的用户
|
||
|
|
query, _ := g.DB().Query(context.Background(), "SELECT id, user_name, phone, openid FROM bus_construction_user WHERE phone IN (SELECT phone FROM bus_construction_user WHERE deleted_at IS NULL AND phone <> '' GROUP BY phone HAVING COUNT(phone) > 1) AND phone IS NOT NULL AND phone <> '' and deleted_at is null ORDER BY phone")
|
||
|
|
if err := query.Structs(&duplUsers); err != nil {
|
||
|
|
t.Fatal(err)
|
||
|
|
}
|
||
|
|
|
||
|
|
var errOpenids []DuplUser
|
||
|
|
duplUsers = lo.FilterMap(duplUsers, func(v DuplUser, _ int) (DuplUser, bool) {
|
||
|
|
// 如果 openid 不包含任何字母，则认为是无效的 openid
|
||
|
|
if !hasLetter(v.Openid) {
|
||
|
|
errOpenids = append(errOpenids, v)
|
||
|
|
return v, false
|
||
|
|
}
|
||
|
|
|
||
|
|
return v, true
|
||
|
|
})
|
||
|
|
|
||
|
|
for _, v := range duplUsers {
|
||
|
|
fmt.Printf("User: %#v\n", v)
|
||
|
|
}
|
||
|
|
|
||
|
|
fmt.Println("错误的OpenID")
|
||
|
|
for _, v := range errOpenids {
|
||
|
|
fmt.Printf("User: %#v\n", v)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// 判断字符串是否包含字母
|
||
|
|
func hasLetter(s string) bool {
|
||
|
|
for _, r := range s {
|
||
|
|
if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') {
|
||
|
|
return true
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return false
|
||
|
|
}
|